Re: PostgreSQL Gotchas

> On 10/6/05, Aly S.P Dharshi <aly ( dot ) dharshi (
at ) telus ( dot ) net> wrote:
>
>
http://sql-info.de/postgresql/postgres-gotchas.html
>
> Any comments from folks on the list ?

- It's a lot shorter than MySQL's gotchas list.
- 8 of the 13 are for versions of PostgreSQL <= 8.1
- Of the remaining, I consider "select as" to be
really trivial (and it appears a work-around can be
hacked).
- lowercase folding. I DO sometimes wish I could use
fieldID, etc. without quoting it.
- I've never found count(*) to be slow.
- I don't know enough about the "UNICODE means
"UTF-8"" and "RANDOM() failures" to comment.

CSN

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

CSN wrote:

>- I don't know enough about the "UNICODE means
>"UTF-8"" and "RANDOM() failures" to comment.
>
>
I'm hardly an expert, but I've done enough with unicode to know that you
can easily convert utf-8 to any other flavor of unicode you might want
to use. Though, why you'd want to use something other than utf-8 in the
first place I don't understand.

On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote:
> - lowercase folding. I DO sometimes wish I could use
> fieldID, etc. without quoting it.

I believe that may be against ANSI SQL. In any case, the only databases
I can think of that don't fold-case in some form are MySQL and Access.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Yep, I think the SQL spec says fold to uppercase. I'm
not sure why PostgreSQL folds to lowercase instead,
but if folding has to occur, I prefer lowercase.

CSN

--- "Jim C. Nasby" <jnasby(at)pervasive(dot)com> wrote:

> On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote:
> > - lowercase folding. I DO sometimes wish I could
> use
> > fieldID, etc. without quoting it.
>
> I believe that may be against ANSI SQL. In any case,
> the only databases
> I can think of that don't fold-case in some form are
> MySQL and Access.
> --
> Jim C. Nasby, Sr. Engineering Consultant
> jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com
> work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf
> cell: 512-569-9461
>

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

CSN wrote:
> Yep, I think the SQL spec says fold to uppercase. I'm
> not sure why PostgreSQL folds to lowercase instead,
> but if folding has to occur, I prefer lowercase.

I think preference was why lowercase was chosen many moons ago. It's
stayed that way because otherwise existing users would be storming the
-hackers list with pitchforks and flaming torches.

--
Richard Huxton
Archonet Ltd

On Fri, Oct 07, 2005 at 08:44:34AM +0100, Richard Huxton wrote:
> CSN wrote:
> >Yep, I think the SQL spec says fold to uppercase. I'm
> >not sure why PostgreSQL folds to lowercase instead,
> >but if folding has to occur, I prefer lowercase.
>
> I think preference was why lowercase was chosen many moons ago. It's
> stayed that way because otherwise existing users would be storming the
> -hackers list with pitchforks and flaming torches.

If any change was made I'm sure it would be to allow the user to decide
which way case was folded. But IMHO, anyone messing around with object
names that won't fold is asking for trouble anyway.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

I use PostgeSQL less than year. Before I worked with MS SQL Server
2000, MySQL 3&4, Oracle8i and Interbase. Also, I studied standards
SQL:1999 and SQL:2003. So, after switching to PostgreSQL I've
encountered with several things that seem strange to me. Of course,
several of them are simply not implemented yet and are in the list of
unsopported features:
http://www.postgresql.org/docs/8.0/interactive/unsupported-features-sql-standard.html.
But some seem to be 'old diseases'.

Here is my list of the issues:
http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc

Perhaps I'm wrong with some issues - any comments are welcome.

On 08/10/05, Jim C. Nasby <jnasby(at)pervasive(dot)com> wrote:
> On Fri, Oct 07, 2005 at 08:44:34AM +0100, Richard Huxton wrote:
> > CSN wrote:
> > >Yep, I think the SQL spec says fold to uppercase. I'm
> > >not sure why PostgreSQL folds to lowercase instead,
> > >but if folding has to occur, I prefer lowercase.
> >
> > I think preference was why lowercase was chosen many moons ago. It's
> > stayed that way because otherwise existing users would be storming the
> > -hackers list with pitchforks and flaming torches.
>
> If any change was made I'm sure it would be to allow the user to decide
> which way case was folded. But IMHO, anyone messing around with object
> names that won't fold is asking for trouble anyway.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Best regards,
Nikolay

On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
> http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
>
> Perhaps I'm wrong with some issues - any comments are welcome.

The only thing I can comment on is updatable views. You can make
updatable views using RULEs. The only thing is that they're not
*automatically* updateable.

In theory, if someone came up will a program that from given <view
definition> produced the appropriate INSERT, UPDATE and DELETE rules,
it might be incorporated. Currently you just have to do it manually...

Case-insensetive text comparisons can be acheived using the citext
module on gborg.

http://gborg.postgresql.org/project/citext/projdisplay.php

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On 08/10/05, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
> > http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
> >
> > Perhaps I'm wrong with some issues - any comments are welcome.
>
> The only thing I can comment on is updatable views. You can make
> updatable views using RULEs. The only thing is that they're not
> *automatically* updateable.

OK, I'll make this correction. But for me, updatable views are views
for which DBMS supports insert/update/delete operations as for
tables. Ideally, people shouldn't distinguish table and view - that's
what theory stands for (see Date's thoutghs about it:
http://www.dbmsmag.com/int9410.html, he also has a cycle of articles
on this theme: http://www.dbdebunk.citymax.com/page/page/622302.htm).
PostgreSQL doesn't support updates even for simple views such as
select-with-restriction. What it does support - not updatable views,
but some kind of INSTEAD OFF triggers (another form of).

>
> In theory, if someone came up will a program that from given <view
> definition> produced the appropriate INSERT, UPDATE and DELETE rules,
> it might be incorporated. Currently you just have to do it manually...

There is a good theory (Date), but it cannot be implemented for any
practical DBMS. The cause lies in differences between theory and
practice. And the major difference is possibility to define tables w/o
PK (in other words, possible duplicate rows). Nevertheless, all major
commercial RDMSs support some subset of views that can be updated..
SQL:2003 defines a quite large subset, but the definition is pretty
mazy...

>
> Case-insensetive text comparisons can be acheived using the citext
> module on gborg.
>
> http://gborg.postgresql.org/project/citext/projdisplay.php

Thanks for the link, I'll try it. However, I suppose that such basic
feature as support collations should be implemented in core.

>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>
>
>

--
Best regards,
Nikolay

On Sat, Oct 08, 2005 at 06:05:29PM +0400, Nikolay Samokhvalov wrote:
> On 08/10/05, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> > The only thing I can comment on is updatable views. You can make
> > updatable views using RULEs. The only thing is that they're not
> > *automatically* updateable.
>
> OK, I'll make this correction. But for me, updatable views are views
> for which DBMS supports insert/update/delete operations as for
> tables. Ideally, people shouldn't distinguish table and view - that's
> what theory stands for (see Date's thoutghs about it:
> http://www.dbmsmag.com/int9410.html, he also has a cycle of articles
> on this theme: http://www.dbdebunk.citymax.com/page/page/622302.htm).
> PostgreSQL doesn't support updates even for simple views such as
> select-with-restriction. What it does support - not updatable views,
> but some kind of INSTEAD OFF triggers (another form of).

You've got me confused. What are INSTEAD OF triggers?

PostgreSQL does support views that look exactly like tables. You can
decide on INSERT what to do whith fields not in the view, which columns
you allow UPDATE and what the semantics should be for DELETE if the
view is a join on multiple tables. All PostgreSQL doesn't do is create
these rules for you.

For an example see here, all the way at the bottom. RULEs are not
TRIGGERs:

http://www.varlena.com/varlena/GeneralBits/82.php

<snip>
> PK (in other words, possible duplicate rows). Nevertheless, all major
> commercial RDMSs support some subset of views that can be updated..
> SQL:2003 defines a quite large subset, but the definition is pretty
> mazy...

PostgreSQL allows any view to be updatable, no matter how complex it
is. You just have to create the rules yourself.

There have been attempts to automate the process, they just havn't been
clean enough to pass muster. And people who really want updateable
views can make them already.

Hope this clarifies things for you,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On 10/8/05, Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> wrote:
> I use PostgeSQL less than year. Before I worked with MS SQL Server
> 2000, MySQL 3&4, Oracle8i and Interbase. Also, I studied standards
> SQL:1999 and SQL:2003. So, after switching to PostgreSQL I've
> encountered with several things that seem strange to me. Of course,
> several of them are simply not implemented yet and are in the list of
> unsopported features:
> http://www.postgresql.org/docs/8.0/interactive/unsupported-features-sql-standard.html.
> But some seem to be 'old diseases'.
>
> Here is my list of the issues:
> http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
>
> Perhaps I'm wrong with some issues - any comments are welcome.
>
>From the referenced page...

"We can insert into column of type TIME the value such as '12:15' and
then obtain '12:15:00' making select, but we couldn't do the same with
dates."

Huh? Minutes and seconds have a valid value of zero. Months and days
don't. Date types need to contain a valid date, and time types need
to contain a valid time. Anything else seems like, well, MySQL.

Nikolay Samokhvalov wrote:

>On 08/10/05, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
>
>
>>On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
>>
>>
>>>http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
>>>
>>>Perhaps I'm wrong with some issues - any comments are welcome.
>>>
>>>
>>The only thing I can comment on is updatable views. You can make
>>updatable views using RULEs. The only thing is that they're not
>>*automatically* updateable.
>>
>>
>
>OK, I'll make this correction. But for me, updatable views are views
>for which DBMS supports insert/update/delete operations as for
>tables. Ideally, people shouldn't distinguish table and view - that's
>what theory stands for (see Date's thoutghs about it:
>http://www.dbmsmag.com/int9410.html, he also has a cycle of articles
>on this theme: http://www.dbdebunk.citymax.com/page/page/622302.htm).
>
>
Who do you mean by "people?" Users? DBA's?

Now, obviously the DBA will always be able to distinguish between them.
Otherwise he/she wouldn't be much of a DBA would he/she?

>PostgreSQL doesn't support updates even for simple views such as
>select-with-restriction. What it does support - not updatable views,
>but some kind of INSTEAD OFF triggers (another form of).
>
>
Ok... Your complaint is that PostgreSQL doesn't support automatically
generating insert/update/delete rules for views. Ultimately, however,
this may not be done simply because there are other priorities which
people are devoting their time and energy to instead. I know some
people have put in some work on this item but I don't know what the
status is.

Also, what SQL Server 2003 calles a trigger, we call a rule. What we
call a trigger is different and I am not sure SQL Server 2003 has such
an equivalent.

>
>
>>In theory, if someone came up will a program that from given <view
>>definition> produced the appropriate INSERT, UPDATE and DELETE rules,
>>it might be incorporated. Currently you just have to do it manually...
>>
>>
>
>There is a good theory (Date), but it cannot be implemented for any
>practical DBMS. The cause lies in differences between theory and
>practice. And the major difference is possibility to define tables w/o
>PK (in other words, possible duplicate rows). Nevertheless, all major
>commercial RDMSs support some subset of views that can be updated..
>SQL:2003 defines a quite large subset, but the definition is pretty
>mazy...
>
>

Again, this is likely doable. There are hidden fields that I suppose
could be extended in a view to reference unique rows (maybe ctid since
the whole thing is expanded in a single SQL statement, but I haven't
tried it).

Best Wishes,
Chris Travers
Metatron Technology Consulting