Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Hartmut Goebel]

Am 03.06.2010 16:15, schrieb Tom Lane:

>> Solution: pg_dump should quote *all* column-names, no matter if they are
>> keywords or not.
> 
> That was considered and rejected long ago.  Readability of the dump
> script is something that we put a nonzero value on.

Sorry, I do not understand this.

I assume you mean readability for humans?!

So if readability is not important, what speaks against always quoting
the column names?

-- 
Schönen Gruß - Regards
Hartmut Goebel
Dipl.-Informatiker (univ.), CISSP, CSSLP

Goebel Consult
Spezialist für IT-Sicherheit in komplexen Umgebungen
http://www.goebel-consult.de

Monatliche Kolumne: http://www.cissp-gefluester.de/
Goebel Consult mit Mitglied bei http://www.7-it.de



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Hartmut Goebel]

Am 03.06.2010 20:07, schrieb Tom Lane:
> "Kevin Grittner"  writes:
>> Hartmut Goebel  wrote:
>>> If upgraded the rpm-packages from 8.3 to 8.4. Then postgres failed
>>> starting (something like "Database version mismatch").
>  
>> You need to be running the old server using 8.3 software and while
>> using pg_dump from 8.4 software.  Does your packager provide some
>> way to install the new version at a different location?  If not, is
>> there a separate machine on which you could install 8.4?
> 
> In practice, if he has to redo the dump, the easiest fix is really
> going to be to rename the column beforehand.  He's likely to end up
> doing that anyway rather than quoting its name forever ...

Both solutions are quite complicated and require a lot of work and
knowledge. Esp. since there seams to be no upgrade or migration guide
available.

(NB: I personally solved the problem using pg_restore | sed | pqsl. But
this bug realy is about a generic problem.)

Given the fact that postgres is not only used in "high end" environments
which have a professional database admin (see below), I strongly suggest
finding a solution which is easier to handle for average admins.

The solution I suggested (simply quoting all column names) would AFAIK
solve this problem once and forever.

An example for Postgresql in a non-database-admin evironment is the
three tier ERP application www.tryton.org. The Tryton admin typically is
not a database guy, but a generic, average server administrator. He
probably knowns about databases, SQL, etc. But he has *a lot* of work
and he is happy about everything which makes his live easier. And he
hates stuff which does not work, while it is commonly expected to work easy.

The Tryton GUI offers backing up the database, which is simply pg_dump
behind. The Tryton admin expects to be able to restore this backup after
upgrade. Because it is such easy to get a database backup, he expects
restore being that easy, too.

The Tryton admin does not understand at first, why this doe not work. It
worked when upgrading 8.1 to 8.2 and when upgrading 8.2 to 8.3. But when
upgrading to 8.4 it does not work.

If the admin is a Mysql-fan, he will be curing on postgres, as soon as
he found out how easy the solution would have been: "Would I have
stayied at mysql, they are able to quote all column names if neccessary.
Sh** postgres!"

And he will be wasting another hour (or more) working around the
problem. While the solution could be *so easy*: simply quote all column
names in pg_dump. (And backport to 8.0, 8.2, 8.3 :-)

-- 
Schönen Gruß - Regards
Hartmut Goebel
Dipl.-Informatiker (univ.), CISSP, CSSLP

Goebel Consult
Spezialist für IT-Sicherheit in komplexen Umgebungen
http://www.goebel-consult.de

Monatliche Kolumne: http://www.cissp-gefluester.de/
Goebel Consult mit Mitglied bei http://www.7-it.de



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Stephen Frost]

* Hartmut Goebel (h.goe...@goebel-consult.de) wrote:
> Am 03.06.2010 16:15, schrieb Tom Lane:
> > That was considered and rejected long ago.  Readability of the dump
> > script is something that we put a nonzero value on.
> 
> I assume you mean readability for humans?!

Yes, readability for humans is important.

> So if readability is not important, what speaks against always quoting
> the column names?

Quoting all column names makes the dump script much more difficult for
human consumption, which is important.

Thanks,

Stephen

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5489: SELECT ... RETURNING INTO ... in ecpg

[Michael Meskes]

> As I saw, ecpg preprocessor is unable to handle "SELECT ... RETURNING INTO
> ... " queries due to the following line in

I take it you mean INSERT/DELETE/UPDATE and not SELECT right? And yes, those
results weren't moved into C space. I more or less applied your patch to 8.3,
8.4 and 9.0, so the next release will work as expected.

Thanks for spotting this.

Michael

-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Stephen Frost]

* Hartmut Goebel (h.goe...@goebel-consult.de) wrote:
> Am 04.06.2010 13:56, schrieb Stephen Frost:
> > Quoting all column names makes the dump script much more difficult for
> > human consumption, which is important.
> 
> I don't agree with you here. But this may be a matter of personal taste.
> 
> Esp. I think, functionality is much ore important than a small decrees
> of readability. At least pg_dump should get an option
> "--quote-column-names", so this can be switcced on if necessary.

Something like '--quote-identifiers' might be alright, so long as it's
defaulted to 'off'.  Of course, I don't know that it'd actually solve
your problem at all- after all, keywords can and will change between
major versions and even if your pg_dump quotes all identifiers, anything
else using the database (eg: applications) would need to as well.
Keyword changes aren't the only thing an application or other DB user
needs to be concerned about when changing major versions of PG either.

If you're using pg_dump for backups, then when you need to restore, it
should be into the same version of PG that you took the pg_dump from.
If you're using pg_dump to upgrade, use the pg_dump from the version
you're upgrading *to*, and do so in a test environment first to make
sure that the restore works correctly, that the applications and other
DB users are happy with the new version, etc, etc, before even thinking
about upgrading a production system.

Note also that having multiple major versions of PG installed (eg: 8.3
and 8.4) at the same time is made easier on some platforms (Debian-based
ones, specifically).

Thanks,

Stephen

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Hartmut Goebel]

Am 04.06.2010 13:56, schrieb Stephen Frost:

> Quoting all column names makes the dump script much more difficult for
> human consumption, which is important.

I don't agree with you here. But this may be a matter of personal taste.

Esp. I think, functionality is much ore important than a small decrees
of readability. At least pg_dump should get an option
"--quote-column-names", so this can be switcced on if necessary.

-- 
Schönen Gruß - Regards
Hartmut Goebel
Dipl.-Informatiker (univ.), CISSP, CSSLP

Goebel Consult
Spezialist für IT-Sicherheit in komplexen Umgebungen
http://www.goebel-consult.de

Monatliche Kolumne: http://www.cissp-gefluester.de/
Goebel Consult mit Mitglied bei http://www.7-it.de



smime.p7s
Description: S/MIME Cryptographic Signature

[BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Farid Zidan]

The following bug has been logged online:

Bug reference:  5490
Logged by:  Farid Zidan
Email address:  fa...@zidsoft.com
PostgreSQL version: 8.4.1
Operating system:   Windows XP 32-bit
Description:Using distinct for select list causes insert of
timestamp string literal to fail
Details: 

If you use keyword 'distinct' for the source select of the insert statement
the insert fails. Insert succeeds if 'distinct' is not used in select list.
Example,

create table test_insert (
col1 char(8) not null,
col2 TIMESTAMP not null default CURRENT_TIMESTAMP);

insert into 
test_insert 
(col1, col2) values
('a', '2010-04-30 00:00:00');

insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'


ERROR:  column "col2" is of type timestamp without time zone but expression
is of type text
LINE 16: '2010-04-30 00:00:00'
 ^
HINT:  You will need to rewrite or cast the expression.

** Error **

ERROR: column "col2" is of type timestamp without time zone but expression
is of type text
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 282

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Tom Lane]

"Farid Zidan"  writes:
> If you use keyword 'distinct' for the source select of the insert statement
> the insert fails. Insert succeeds if 'distinct' is not used in select list.

This isn't a bug, it's a consequence of the fact that you're not
specifying the types of the literal constants.  DISTINCT forces
the parser to assign a data type to the constants (otherwise there
is no way to understand what duplicate-elimination means) and what
it will fall back to is "text".  Try attaching an explicit cast,
eg
'2010-04-30 00:00:00'::timestamp

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Kevin Grittner]

"Farid Zidan"  wrote:
 
> insert into test_insert
> (col1, col2)
> select distinct
> 'b',
> '2010-04-30 00:00:00'
> 
> 
> ERROR:  column "col2" is of type timestamp without time zone but
> expression is of type text
> LINE 16: '2010-04-30 00:00:00'
>  ^
> HINT:  You will need to rewrite or cast the expression.
 
Try using a timestamp literal instead of a bare literal:
 
insert into test_insert
(col1, col2)
select distinct
'b',
timestamp '2010-04-30 00:00:00'
 
This is actually working as intended in all the cases you showed, so
it isn't a bug.  If we were strictly complying with the SQL
standard, your first example would also fail, but we are more
lenient than the standard where we can be, to allow an unadorned
literal to be an UNKNOWN type until something causes it to be
resolved, to allow people to omit the type decoration in many cases.
To determine that something is a distinct value, you have to
determine a type for it (otherwise you won't know if '2010-04-30
00:00:00' is the same as '2010-04-30 00:00:00.0', for example), so
if you don't tell it otherwise, it will assume text -- leading to
the behavior you saw.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Kevin Grittner]

>Farid Zidan  wrote:
 
>> If we were strictly complying with the SQL standard,
 
> Considering the statement works in all the 9 DBMS systems+ that I
> have tested so far as mentioned above, I would say PostgreSQL is
> not compliant with SQL standard in this regard.
 
The SQL standard is a document published by the International
Standards Organization (ISO) and also adopted by the American
National Standards Institute (ANSI).  Those documents don't require
a query in either of the forms you presented to work.  Because of
the convenience factor, most database products have non-standard
extensions to omit type specification in some places.  PostgreSQL's
extensions are oriented more toward user-installable data types
(such as geometric shapes or global coordinates), so the particulars
of our non-standard extensions differ so that use of those features
is as easy as practicable.  That does result in some non-standard
extensions which work in other products not working in PostgreSQL. 
 
I think you'll find that the syntax I suggested (using the standard
timestamp literal instead of a bare character string literal) will
work in all of the databases you mentioned; if you want portable
code, it is best to follow the standard rather than some inferred
popular convention.
 
I hope this helps.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Kevin Grittner]

>Farid Zidan  wrote:
 
> can be eliminated by appropriately handling the distinct keyword
> and does not have to occur.
 
Based on previous discussions around our approaching data types, I
don't think any of the regular PostgreSQL developers are likely to
agree with you; but if you see a way to make it work, feel free to
submit a patch.  See this page for the process:
 
http://wiki.postgresql.org/wiki/Submitting_a_Patch
 
> The ISO-datetime string literal format I am using the most
> general/standard for datetime/timestamp and is not the issue here.
 
The format in your string literal is the portable one; however, a
timestamp literal requires the TIMESTAMP keyword ahead of the string
literal, which you have chosen to omit.  Did you try the query with
a proper timestamp literal, as I suggested, against all these
databases?  If using standard syntax works, why not use it?
 
> The 'distinct' keyword is causing the error.
 
No, non-standard syntax is causing the error in the case of
DISTINCT, because our extension to the standard does not cover that
case, even though it covers the other.  There are good reasons for
that, which you'll probably discover in short order if you work on a
patch for the issue.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Hartmut Goebel]

Am 04.06.2010 14:57, schrieb Stephen Frost:
> * Hartmut Goebel (h.goe...@goebel-consult.de) wrote:
>> Am 04.06.2010 13:56, schrieb Stephen Frost:
>>> Quoting all column names makes the dump script much more difficult for
>>> human consumption, which is important.
>>
>> I don't agree with you here. But this may be a matter of personal taste.
>>
>> Esp. I think, functionality is much ore important than a small decrees
>> of readability. At least pg_dump should get an option
>> "--quote-column-names", so this can be switcced on if necessary.
> 
> Something like '--quote-identifiers' might be alright, so long as it's
> defaulted to 'off'.  Of course, I don't know that it'd actually solve
> your problem at all- after all, keywords can and will change between
> major versions and even if your pg_dump quotes all identifiers, anything
> else using the database (eg: applications) would need to as well.

The application already quotes all column names :-) It's using a generic
framework which does not (and must not) rely on column names being
non-keywords.

> If you're using pg_dump to upgrade, use the pg_dump from the version
> you're upgrading *to*, and do so in a test environment first to make
> sure that the restore works correctly, that the applications and other
> DB users are happy with the new version, etc, etc, before even thinking
> about upgrading a production system.

This is correct -- in theory. In practice there are many average system
administrators which need an easy upgrade path. You may call this
unprofessional, but this is reality.

To put it on the point: Is postgres meant for average administrators or
for elite database admins? In the first case, developers should think
about how to make work easier for the average ones.

-- 
Schönen Gruß - Regards
Hartmut Goebel
Dipl.-Informatiker (univ.), CISSP, CSSLP

Goebel Consult
Spezialist für IT-Sicherheit in komplexen Umgebungen
http://www.goebel-consult.de

Monatliche Kolumne: http://www.cissp-gefluester.de/
Goebel Consult mit Mitglied bei http://www.7-it.de



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Farid Zidan]

On 6/4/2010 11:53 AM, Tom Lane wrote:

  DISTINCT forces
the parser to assign a data type to the constants (otherwise there
is no way to understand what duplicate-elimination means) and what
it will fall back to is "text"

I am including the column list for the insert, so parser knows col2
data type is TIMESTAMP and it has to convert from text to timestamp to
do the insert.

It should be able to do that without generating an error. It is the
same select list, the same data types, nothing has changed except using
the 'distinct' keyword to eliminate duplicates. The parse behavior
after duplicates have been eliminated should be the same as when
'distinct' is not used.

Whether 'distinct' is used or not should not affect the semantics of
the insert statement (it should only remove duplicate rows). 

I have used this statement in Firebrid, MS SQL Server, Oracle, MySQL,
SQLAnywhere, DB2, Derby, Informix, etc, and all of them do not generate
an error
because I need to use 'distinct' to eliminate duplicates from being
inserted.


  If we were strictly complying with the SQL
standard, 

Considering the statement works in all the 9 DBMS systems+ that I have
tested so far as mentioned above, I would say PostgreSQL is not
compliant with SQL standard in this regard.

I guess, what I am saying, is that what the parser is doing is not the
desired behavior. I understand there are technical things going on
behind
the scene, but that's what needs to be fixed to ensure PostgreSQL
compatibility with SQL standard and interoperability with generic sql
statements. 

best regards,
Farid

On 6/4/2010 11:57 AM, Kevin Grittner wrote:

  "Farid Zidan"  wrote:
 
  
  
insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'


ERROR:  column "col2" is of type timestamp without time zone but
_expression_ is of type text
LINE 16: '2010-04-30 00:00:00'
 ^
HINT:  You will need to rewrite or cast the _expression_.

  
   
Try using a timestamp literal instead of a bare literal:
 
insert into test_insert
(col1, col2)
select distinct
'b',
timestamp '2010-04-30 00:00:00'
 
This is actually working as intended in all the cases you showed, so
it isn't a bug.  If we were strictly complying with the SQL
standard, your first example would also fail, but we are more
lenient than the standard where we can be, to allow an unadorned
literal to be an UNKNOWN type until something causes it to be
resolved, to allow people to omit the type decoration in many cases.
To determine that something is a distinct value, you have to
determine a type for it (otherwise you won't know if '2010-04-30
00:00:00' is the same as '2010-04-30 00:00:00.0', for example), so
if you don't tell it otherwise, it will assume text -- leading to
the behavior you saw.
 
-Kevin


  


-- 

Signature

www.zidsoft.com
CompareData:  compare
and synchronize SQL DBMS data visually between two databases
using ODBC drivers

Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Farid Zidan]

Title: Signature




Hello Kevin,

My bug report is about using 'distinct' in the select list which is
causing a side-effect. That's why I classify this as a bug. Distinct
should not have unintended side-effects.

This side-effect is implementation-dependent and is manifested in the
current PostgreSQL query processing but can be eliminated by
appropriately handling the distinct keyword and does not have to occur.


The ISO-datetime string literal format I am using the most
general/standard for datetime/timestamp and is not the issue here. The
'distinct' keyword is causing the error.

Farid

On 6/4/2010 12:52 PM, Kevin Grittner wrote:

  
Farid Zidan  wrote:

  
   
  
  

  If we were strictly complying with the SQL standard,
  

  
   
  
  
Considering the statement works in all the 9 DBMS systems+ that I
have tested so far as mentioned above, I would say PostgreSQL is
not compliant with SQL standard in this regard.

  
   
The SQL standard is a document published by the International
Standards Organization (ISO) and also adopted by the American
National Standards Institute (ANSI).  Those documents don't require
a query in either of the forms you presented to work.  Because of
the convenience factor, most database products have non-standard
extensions to omit type specification in some places.  PostgreSQL's
extensions are oriented more toward user-installable data types
(such as geometric shapes or global coordinates), so the particulars
of our non-standard extensions differ so that use of those features
is as easy as practicable.  That does result in some non-standard
extensions which work in other products not working in PostgreSQL. 
 
I think you'll find that the syntax I suggested (using the standard
timestamp literal instead of a bare character string literal) will
work in all of the databases you mentioned; if you want portable
code, it is best to follow the standard rather than some inferred
popular convention.
 
I hope this helps.
 
-Kevin


  


-- 



www.zidsoft.com
CompareData:  compare
and synchronize SQL DBMS data visually between two databases
using ODBC drivers

Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Farid Zidan]

Hello Kevin,

I strongly disagree with your analysis of this issue. Like I said, this
syntax works with 9 different databases, so obviously whatever
PosgreSQL query procesor is doing in this case is not the desired
behavior.

To ensure PosgreSQL success, the query processor must behave in a
compliant manner with established standards whether those standards are
set by SQL ISO specs or are de facto standards.

It is too much asking developers to change their sql to overcome
implementation-dependent side-effects of PostgreSQL query processor. If
a simple SQL statement works on 9+ different databases, then it should
also work in PostreSQL with no need for developers to special-code for
PostgreSQL. Very basic feature is converting a string literal to a
datetime/timestamp value and developers should not do any
special coding to accomplish this simple conversion. '2010-04-30
00:00:00' should convert to timestamp in PostgreSQL with no other flags
or syntax
decoration (it already does except when 'distinct' is used).

Compatibility is very high on desired features for a DBMS and is a
requirement for smooth porting of applications from other databases to
PostreSQL and cross-dbms applications. It really boils down to making
it work, technical details are what developers love and I am sure
PostgreSQL developers can make this simple sql insert work on PostreSQL
just like all the other developers have done for the other DBMSs.

Anyway, I have reported this issue because I encountered it and it
negatively impacts my project. I don't
expect it to be fixed right now, that's something that PostgreSQL
developers can debate and prioritize. I only ask that this issue is
identified, since it does not work in my case when the target dbms is
PostgreSQL and I am sure it can impact other developers projects and it
would need to be addressed at
some point in the future with a solution where it just work like it
does in all the other DBMSs.

Farid


On 6/4/2010 1:36 PM, Kevin Grittner wrote:

  
Farid Zidan  wrote:

  
   
  
  
can be eliminated by appropriately handling the distinct keyword
and does not have to occur.

  
   
Based on previous discussions around our approaching data types, I
don't think any of the regular PostgreSQL developers are likely to
agree with you; but if you see a way to make it work, feel free to
submit a patch.  See this page for the process:
 
http://wiki.postgresql.org/wiki/Submitting_a_Patch
 
  
  
The ISO-datetime string literal format I am using the most
general/standard for datetime/timestamp and is not the issue here.

  
   
The format in your string literal is the portable one; however, a
timestamp literal requires the TIMESTAMP keyword ahead of the string
literal, which you have chosen to omit.  Did you try the query with
a proper timestamp literal, as I suggested, against all these
databases?  If using standard syntax works, why not use it?
 
  
  
The 'distinct' keyword is causing the error.

  
   
No, non-standard syntax is causing the error in the case of
DISTINCT, because our extension to the standard does not cover that
case, even though it covers the other.  There are good reasons for
that, which you'll probably discover in short order if you work on a
patch for the issue.
 
-Kevin


  


-- 

Signature

www.zidsoft.com
CompareData:  compare
and synchronize SQL DBMS data visually between two databases
using ODBC drivers

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Kevin Grittner]

Hartmut Goebel  wrote:
 
> The application already quotes all column names :-) It's using a
> generic framework which does not (and must not) rely on column
> names being non-keywords.
 
Same here.  I suspect that this is much more commonn than many
PostgreSQL developers realize; and I think it makes a reasonable
case for at least an *option* to quote all identifiers emitted by
pg_dump.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Bruce Momjian]

Kevin Grittner wrote:
> Hartmut Goebel  wrote:
>  
> > The application already quotes all column names :-) It's using a
> > generic framework which does not (and must not) rely on column
> > names being non-keywords.
>  
> Same here.  I suspect that this is much more commonn than many
> PostgreSQL developers realize; and I think it makes a reasonable
> case for at least an *option* to quote all identifiers emitted by
> pg_dump.

Even if we quote them in the dump, I assume applications would need to
quote them too, which I doubt many do.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[David Fetter]

On Fri, Jun 04, 2010 at 02:59:48PM -0400, Bruce Momjian wrote:
> Kevin Grittner wrote:
> > Hartmut Goebel  wrote:
> >  
> > > The application already quotes all column names :-) It's using a
> > > generic framework which does not (and must not) rely on column
> > > names being non-keywords.
> >  
> > Same here.  I suspect that this is much more commonn than many
> > PostgreSQL developers realize; and I think it makes a reasonable
> > case for at least an *option* to quote all identifiers emitted by
> > pg_dump.
> 
> Even if we quote them in the dump, I assume applications would need
> to quote them too, which I doubt many do.

It seems like something that's doable by pg_dump as a "default off"
option.  TODO for 9.1?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Kevin Grittner]

David Fetter  wrote:
 
> It seems like something that's doable by pg_dump as a "default
> off" option.  TODO for 9.1?
 
Sounds good to me.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Bruce Momjian]

David Fetter wrote:
> On Fri, Jun 04, 2010 at 02:59:48PM -0400, Bruce Momjian wrote:
> > Kevin Grittner wrote:
> > > Hartmut Goebel  wrote:
> > >  
> > > > The application already quotes all column names :-) It's using a
> > > > generic framework which does not (and must not) rely on column
> > > > names being non-keywords.
> > >  
> > > Same here.  I suspect that this is much more commonn than many
> > > PostgreSQL developers realize; and I think it makes a reasonable
> > > case for at least an *option* to quote all identifiers emitted by
> > > pg_dump.
> > 
> > Even if we quote them in the dump, I assume applications would need
> > to quote them too, which I doubt many do.
> 
> It seems like something that's doable by pg_dump as a "default off"
> option.  TODO for 9.1?

This is the bug report that prompted this thread:

http://archives.postgresql.org/pgsql-bugs/2010-06/msg00018.php

I bigger question is why wouldn't we backpatch WINDOW as quoted in
pg_dump when we release back-branches?  That would make the bug go away,
rather than require users to use a special flag (and find out only after
they were doing the reload).

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Greg Stark]

On Fri, Jun 4, 2010 at 7:18 PM, Farid Zidan  wrote:
> If a simple SQL statement works on 9+ different databases

For what it's worth are you sure it works as you expect in these other
databases?

I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
this does:

 select cast(x as timestamp with time zone) from (select distinct x
from (values ('2010-01-01 12:00PM UTC'), ('2010-01-01 7:00AM EST')) as
x(x)) as y;

If you inserted those values into a table with a timestamp with time
zone column you would get duplicate values even with the distinct.

This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.

-- 
greg

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] superuser unable to modify settings of a system table

[Robert Haas]

On Thu, Jun 3, 2010 at 1:21 PM, Tom Lane  wrote:
> Gurjeet Singh  writes:
>> allow_system_table_mods needs a restart :( .Yet another parameter I wish was
>> changeable on the fly.
>
> I'm not sure there's any compelling reason why it couldn't be SUSET.
> Maybe a TODO ...

Personally, I think it would be better to put some work into making
allow_system_table_mods a little less simple-minded.  Right now,
!allow_system_table_mods prohibits you from doing perfectly sensible
things (as in the OP's original example) yet still allows you to do
things that are totally nuts (like DELETE FROM pg_class, which causes
every subsequent connection attempt for that database to panic).
Perfection may be too much to ask for but I'd take "modest
improvement"...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] superuser unable to modify settings of a system table

[Tom Lane]

Robert Haas  writes:
> Personally, I think it would be better to put some work into making
> allow_system_table_mods a little less simple-minded.  Right now,
> !allow_system_table_mods prohibits you from doing perfectly sensible
> things (as in the OP's original example) yet still allows you to do
> things that are totally nuts (like DELETE FROM pg_class, which causes
> every subsequent connection attempt for that database to panic).
> Perfection may be too much to ask for but I'd take "modest
> improvement"...

Nope, that is the wrong viewpoint entirely.  allow_system_table_mods
is intended to prevent you from modifying the *structure* of the
system catalogs, which is fairly critical because the backend C code
tends to depend on that.  Modifying the *content* of the catalogs
is another matter, and in fact we let any superuser do that without
having set allow_system_table_mods.  There is no practical way to
distinguish a benign catalog-content change from a disastrous one,
so we don't try.

It's possible that reloptions is a special case and we should treat it
as being more nearly in the content than structure category.  Not sure.

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Bernd Helmle]

--On 4. Juni 2010 15:19:42 -0400 Bruce Momjian  wrote:


That would make the bug go away,
rather than require users to use a special flag (and find out only after
they were doing the reload).


Out of curiosity, why is this a "bug" now? We recommend migration 
procedures always to use the pg_dump of the newer version because of many 
reasons as clearly stated here:


   

And wouldn't introducing backpatching such behavorial changes to pg_dump 
violate our policy in *not* to change such things in minor releases? (think 
of diff's against schema-only dumps and so on).


   Bernd


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Tom Lane]

Bernd Helmle  writes:
> Out of curiosity, why is this a "bug" now?

It isn't...

> And wouldn't introducing backpatching such behavorial changes to pg_dump 
> violate our policy in *not* to change such things in minor releases?

That was just an off-the-cuff idea, it has certainly not become policy
(and won't, I think).

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] superuser unable to modify settings of a system table

[Robert Haas]

On Fri, Jun 4, 2010 at 4:53 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> Personally, I think it would be better to put some work into making
>> allow_system_table_mods a little less simple-minded.  Right now,
>> !allow_system_table_mods prohibits you from doing perfectly sensible
>> things (as in the OP's original example) yet still allows you to do
>> things that are totally nuts (like DELETE FROM pg_class, which causes
>> every subsequent connection attempt for that database to panic).
>> Perfection may be too much to ask for but I'd take "modest
>> improvement"...
>
> Nope, that is the wrong viewpoint entirely.  allow_system_table_mods
> is intended to prevent you from modifying the *structure* of the
> system catalogs, which is fairly critical because the backend C code
> tends to depend on that.  Modifying the *content* of the catalogs
> is another matter, and in fact we let any superuser do that without
> having set allow_system_table_mods.  There is no practical way to
> distinguish a benign catalog-content change from a disastrous one,
> so we don't try.
>
> It's possible that reloptions is a special case and we should treat it
> as being more nearly in the content than structure category.  Not sure.

The backend C code also depends on the critical system indexes being
present in the system catalogs, yet we still allow them to be deleted.
 Is there really a use case for users fiddling with pg_proc, pg_class,
etc. directly?

At any rate, I'd be happy to drop that part of the proposal.  It would
be a step forward just to permit (even without
allow_system_table_mods) those changes which don't alter the structure
of the catalog.  For ALTER TABLE, the SET STATISTICS, (RE)SET
(attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and
(RE)SET (reloptions) forms are all things that fall into this
category, I believe.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] superuser unable to modify settings of a system table

[Tom Lane]

Robert Haas  writes:
>  Is there really a use case for users fiddling with pg_proc, pg_class,
> etc. directly?

There's a use case for *superusers* to fiddle with them, yes.
(Superusers are presumed to be adults.)  I think I recommend a quick
UPDATE on some catalog at least once a month on the lists.

You might care to consider the fact that no modern Unix system prevents
root from doing rm -rf /, even though that's "obviously" disastrous.
Yet (stretching the analogy all out of shape) there's no convenient user
tool for rearranging the contents of all the inodes on a filesystem.

> At any rate, I'd be happy to drop that part of the proposal.  It would
> be a step forward just to permit (even without
> allow_system_table_mods) those changes which don't alter the structure
> of the catalog.  For ALTER TABLE, the SET STATISTICS, (RE)SET
> (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and
> (RE)SET (reloptions) forms are all things that fall into this
> category, I believe.

It would be far less work to just drop allow_system_table_mods to SUSET.
And we wouldn't get questions about which forms of ALTER TABLE require
it.

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Alvaro Herrera]

Excerpts from Kevin Grittner's message of vie jun 04 14:53:17 -0400 2010:

> Same here.  I suspect that this is much more commonn than many
> PostgreSQL developers realize; and I think it makes a reasonable
> case for at least an *option* to quote all identifiers emitted by
> pg_dump.

I don't think "dumps must be human-readable" is an argument to reject
such a switch, as long as it's off by default.  And I haven't seen any
other valid argument either, so +1 from me.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Kevin Grittner]

>Farid Zidan  wrote:
 
> but when it gets to use the resultset of the subquery in the
> insert it "forgets" how to convert '2010-04-30 00:00:00' to
> timestamp value
 
Not really.  In versions prior to 8.3 it did automagically convert
like that.  PostgreSQL has some pretty fancy features involving
custom data types where this magic caused problems, so a deliberate
decision was taken to no longer provide automatic casts from text to
other data types.
 
> (but forgets only when 'distinct' is used in the subquery!)
 
That is because (as I tried to explain earlier, but apparently
didn't do a good job of communicating), an unadorned literal in
single quotes is *not* taken to be a character string in PostgreSQL.
Its type is held as "unknown" until it is forced to be resolved in
some operation.  This allows easier coding of custom data types, but
does create a few deviations from standard behavior in corner cases,
and breaks from the non-standard "conventional" behavior of many
other databases.  Because of this design choice, for example, the
FAA can more easily write the code they use to map their runways and
other airport facilities.
 
The cost is that in situations such as you describe, you need to
force the type before it is used in the comparisons necessary to
determine a distinct value.  The only way to get the behavior you
want without breaking a great many useful cases, would be to
determine where the result was going to be later used, and use that
information to force the type to something other than text (the
default, when no other information is available).  That would be a
*major* and destabilizing change.
 
For those reasons, the chance of getting *anybody* here to consider
this a bug are close to nil.  The choice to more conveniently handle
advanced cases at the expense of occasionally needing to specify a
type is unlikely to be reversed, to put it mildly.

I can't help but wonder why you resist using the standard syntax. 
The reason the standard exists is to help those trying to write
portable code, so they don't have to count on the vagaries of
"parallel evolution."
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] superuser unable to modify settings of a system table

[Robert Haas]

On Fri, Jun 4, 2010 at 5:13 PM, Tom Lane  wrote:
> Robert Haas  writes:
>>  Is there really a use case for users fiddling with pg_proc, pg_class,
>> etc. directly?
>
> There's a use case for *superusers* to fiddle with them, yes.
> (Superusers are presumed to be adults.)  I think I recommend a quick
> UPDATE on some catalog at least once a month on the lists.
>
> You might care to consider the fact that no modern Unix system prevents
> root from doing rm -rf /, even though that's "obviously" disastrous.
> Yet (stretching the analogy all out of shape) there's no convenient user
> tool for rearranging the contents of all the inodes on a filesystem.

Sure.  I guess it boils down to how much use case you think there is
for updating system catalogs directly (rather than using DDL).  I
don't follow all the lists so I haven't seen these recommendations.

>> At any rate, I'd be happy to drop that part of the proposal.  It would
>> be a step forward just to permit (even without
>> allow_system_table_mods) those changes which don't alter the structure
>> of the catalog.  For ALTER TABLE, the SET STATISTICS, (RE)SET
>> (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and
>> (RE)SET (reloptions) forms are all things that fall into this
>> category, I believe.
>
> It would be far less work to just drop allow_system_table_mods to SUSET.
> And we wouldn't get questions about which forms of ALTER TABLE require
> it.

I think there's some value in distinguishing between things which are
"only for adults" and things which are "almost certainly a bad idea".

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Farid Zidan]

Title: Signature




Hello Greg,


  I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
  

That's a good point. I think you are correct. When the query parser is
in the nested subselect it only sees string literals for the timestamp
column values (does not know it is a timestamp yet). However, when it
gets to do the insert it then must convert the string literals to
timestamp values because at that point it knows that the string literal
is to be inserted into a timestamp column.

Since I am using a constant string literal for the timestamp
it really does not matter when the conversion takes place.

select distinct
,
,
..
'2010-04-30 00:00:00'
from


the timestamp string literal is a constant and really does not
affect the distinct resultset in anyway. I do need to stamp all the
inserts with a specific timestamp value and that's why I am using a
constant string literal. If I used an _expression_ such as
current_timestamp/(ODBC {fn now()}, then that would factor into the
distinct clause and pollute the distinctness of subquery reulsultset.

Here is actual statements I am running and like I said they work for
all 9+ DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax
for the current user ID):
insert into
in_sync_node_toolbar
(node_no, sync_cd, toolbar_cd,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id)
select distinct
isnr.node_no,
case
  when isr.rs_type_cd = 'TABLELS' then 'CMPTS'
  when isr.rs_type_cd = 'PROCLS' then 'CMPPROCS'
  when isr.rs_type_cd = 'SEQLS' then 'CMPSEQS'
  else null
end,
'TBCSCPT1',
'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }
from
in_sync_node_resultset isnr,
in_sync_object_pattern isop,
in_sync_resultset isr
where
(isnr.rs_oid = isr.rs_oid or
 isnr.rs_oid_other = isr.rs_oid) and
isr.rs_oid = isop.rs_oid and
isr.rs_type_cd in ('TABLELS', 'PROCLS', 'SEQLS');

insert into
in_sync_node_toolbar
(node_no, sync_cd, toolbar_cd,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id)
select distinct
isnr.node_no,
case
  when isr.rs_type_cd = 'TBLVIEW' then 'CMPTABLE'
  when isr.rs_type_cd = 'PROC' then 'CMPPROC'
  when isr.rs_type_cd = 'SEQ' then 'CMPSEQ'
  else null
end,
'TBCSCPT1',
'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }
from
in_sync_node_resultset isnr,
in_sync_object iso,
in_sync_resultset isr
where
(isnr.rs_oid = isr.rs_oid or
 isnr.rs_oid_other = isr.rs_oid) and
isr.rs_oid = iso.rs_oid and
isr.rs_type_cd in ('TBLVIEW', 'PROC', 'SEQ');

This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.

All the other DBMSs doing is a select distinct on the subquery that has
the constant timestamp string literals. There is nothing
non-standard or ambiguous there. As far as the DBMS is concerned the
constant string _expression_ is just a string literal and can represent
anything.

Now the issue is that when the other DBMSs get to do the insert part
they are able, as one would expect, to convert the subquery resultset
string literal column to a timestamp column. I think PostreSQL is doing
the first part (subquery with distinct clause correctly), but when it
gets to use the resultset of the subquery in the insert it "forgets"
how to convert 
'2010-04-30 00:00:00' to timestamp value (but forgets only when
'distinct' is used in the subquery!)

Farid

On 6/4/2010 4:18 PM, Greg Stark wrote:

  On Fri, Jun 4, 2010 at 7:18 PM, Farid Zidan  wrote:
  
  
If a simple SQL statement works on 9+ different databases

  
  
For what it's worth are you sure it works as you expect in these other
databases?

I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
this does:

 select cast(x as timestamp with time zone) from (select distinct x
from (values ('2010-01-01 12:00PM UTC'), ('2010-01-01 7:00AM EST')) as
x(x)) as y;

If you inserted those values into a table with a timestamp with time
zone column you would get duplicate values even with the distinct.

This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.

  


-- 



www.zidsoft.com
CompareData:  compare
and synchronize SQL DBMS data visually between two databases
using ODBC drivers

[BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Farid Zidan]

Title: Signature




Hello Kevin,


  I can't help but wonder why you resist using the standard syntax. 
  

I am using the standard syntax. Single quote in sql denotes a string.
so '2010-04-30 00:00:00' is string literal. That's universal. Now you
want me to use PG-specific timestamps and that's like I said is not
standard/cross-dbms.

I have just finished testing with Ingre 9.2 and it works there too.
That's 10 DBMSs systems that use single quotes to denote a string
literal and can covert ISO-standard datetime string literal to
timestamp.

You can't not interpret string literals one way in one statement and
just because user uses the word 'distinct' decide to switch paradigms.
That's not good design or planning. Of course you can decide to do
whatever you want, just do not expect developers to start
special-coding just for PostreSQL because you decide to cast correctly
or not correctly depending on whim.

Let me reiterate the example, maybe it was too terse and you did not
read it carefully,

create table test_insert (
col1 char(8) not null,
col2 TIMESTAMP not null default CURRENT_TIMESTAMP);

>create the test table. No issue.

insert into 
test_insert 
(col1, col2) values
('a', '2010-04-30 00:00:00');

>Works like expected, PG correctly converts standard ISO-datetime
string literal to timestamp. No issue.

insert into test_insert
(col1, col2)
select
'b',
'2010-04-30 00:00:00'

>That works too. No issue.

insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'

>Does not work. That's a bug.

Now this not rocket science, it's simple insert statement where we do
not want duplicates inserted. Works on 10 other DBMSs.

FAA stuff and other is not related to this bug. I would think the FAA
and other organizations want a standard-compliant DBMS system that
knows how to convert a simple ISO-formatted valid string literal to a
timestamp value in more than one variation of sql statement.

You can ignore this bug report and do whatever you want, just do not
say this is an accepted, standard or desired behavior of the server or
is by design. It's not by design that the error happens it is by faulty
handling of the distinct keyword.

I think you have all the information you need to debate and resolve
this issue. If you need any other information you can contact me and I
will be happy to oblige.

Farid


On 6/4/2010 5:40 PM, Kevin Grittner wrote:

  I can't help but wonder why you resist using the standard syntax. 
The reason the standard exists is to help those trying to write
portable code, so they don't have to count on the vagaries of
"parallel evolution."
  


-- 



www.zidsoft.com
CompareData:  compare
and synchronize SQL DBMS data visually between two databases
using ODBC drivers

Re: [BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Kris Jurka]

On Fri, 4 Jun 2010, Farid Zidan wrote:


Here is actual statements I am running and like I said they work for all 9+
DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax for the
current user ID):

'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }



If you're into using standard ODBC escapes for portability, shouldn't you 
be using {ts '2010-04-30 00:00:00'}?


http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx

Kris Jurka

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Greg Stark]

On Fri, Jun 4, 2010 at 11:15 PM, Farid Zidan  wrote:
> Now this not rocket science, it's simple insert statement where we do not
> want duplicates inserted. Works on 10 other DBMSs.
>

I find usually when one person is arguing something is complex and
someone else is arguing it's simple it's the person who's claiming
it's simple who is wrong.

The other databases are not, I believe, preventing duplicates from
being inserted as you describe. They are removing duplicates from the
string constants and then silently converting to a different datatype
before inserting. When postgres removed these default casts to text it
turned up many instances where users had buggy code and Postgres had
been hiding from them by silently using string operators which was not
what users were expecting. In other words, while it might not matter
in this case, in general if you code in this style your code is buggy
and these other database implementations are not doing you any favours
by making it appear to work correctly most of the time.

-- 
greg

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[tomas]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, Jun 04, 2010 at 06:15:09PM -0400, Farid Zidan wrote:

[...]
> Let me reiterate the example, maybe it was too terse and you did not
> read it carefully,

No. I think most readers here have understood your problem perfectly.
Don't underestimate the folks here.

[...]

> Now this not rocket science, it's simple insert statement where we do
> not want duplicates inserted. Works on 10 other DBMSs.

Except on those "other 10 DBMSs" you are most probably getting
(silently!) something different as you'd expect (DSTINCT interpreted as
text, whereas you are "seeing" timestamps). How is that better?

> FAA stuff and other is not related to this bug. I would think the FAA
> and other organizations want a standard-compliant DBMS system that
> knows how to convert a simple ISO-formatted valid string literal to a
> timestamp value in more than one variation of sql statement.

Except that the behaviour of those "other 10 DBMSs" is *beyond
standard*, the standard just rules the case where you state explicitly
the type of the constant. You will find multitude of cases where DMBSs
differ on those cases beyond standard -- that's due to different design
decisions.

What Kevin was trying to convey is that PostgreSQL's design decisions
allow its users to do things other DBMSs can't -- and that's the price
we'll have to pay. Note that behaviour is still within the standard
(and not, as you seem to suggest), so not really a problem: you can
write the query in a way which will work on "all those 11 DBMSs": just
stick to the standard.

> You can ignore this bug report and do whatever you want, just do not
> say this is an accepted, standard or desired behavior of the server or
> is by design. It's not by design that the error happens it is by faulty
> handling of the distinct keyword.

Accepted -- by whom? Standard -- which standard? (because it is not
required by ISO/ANSI, and there is no other "SQL standard" that I'm
aware of).

Regards
- -- tomás
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFMCdHgBcgs9XrR2kYRAjfsAJ0WVvm3AiFfN2jqIc24dqHVbyXM0QCeJqiQ
I31OBlckZ7go48bXZx+YRpQ=
=a7Pw
-END PGP SIGNATURE-

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs