[BUGS] Date Representation Bug. Timezone and update on an already posted as #208 reportbug
Gianfranco Pesce ([EMAIL PROTECTED]) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Date Representation Bug. Timezone and update on an already posted as #208 reportbug
Long Description
The problem of date representation of 22/05/1977 and 28/05/1978 is
related with time zone.
The system is a Linux RedHat 6.2 (also tested on Redhat 7.0 and 7.1) with postegresql
6.5 and 7.1.
ONLY 22/05/1977 and 28/05/1978 are bad recorded into the db. This dates are stored (or
retrieved) as 21/05/1977 ans 27/05/1978 respectively. ALL other date correcly stored.
If I modify my timezone from MET (DayLight Saving Time Enabled) to PDT
the bug disappears.
Sample Code
echo $PGDATESTYLE
SQL,EUROPEAN
test=> create table pippo (d date);
CREATE
test=> insert into pippo values ('22/05/1977');
INSERT 629024 1
test=> insert into pippo values ('28/05/1978');
INSERT 629025 1
test=> insert into pippo values ('30/10/1964');
INSERT 629026 1
test=> select * from pippo;
d
21/05/1977
27/05/1978
30/10/1964
(3 rows)
test=> select * from pippo where d = '22/05/1977';
d
21/05/1977
(1 rows)
No file was uploaded with this report
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Date Representation Bug. Timezone and update on an already posted as #208 reportbug
[EMAIL PROTECTED] writes: > The problem of date representation of 22/05/1977 and 28/05/1978 is > related with time zone. What do you get if you coerce the funny dates to timestamp, eg, select '22/05/1977'::date::timestamp; regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Re: Postgresql date bug
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> if I insert with
> insert into pippo values ('22/05/1977 01:00:00');
> all goes ok.
> with
> insert into pippo values ('22/05/1977 00:59:00');
> the result is wrong! Is it related with daylight saving time?
Possibly, but why just those dates? AFAICT those are not DST
transition days in MET zone (correct?) so I don't see why they'd
be affected and not other days.
Can anyone else reproduce this? I don't see it happening on my
machines, either in 7.0.* or 7.1. I think there must be some
platform dependency involved.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] pg_ctl restart bug
"pg_ctl restart" fails if anything is quoted in postmaster.opts. $ pg_ctl restart Smart Shutdown request at Fri Apr 20 10:11:38 2001 postmaster successfully shut down postmaster successfully started /usr/bin/postmaster: invalid argument -- '-D' Try '/usr/bin/postmaster --help' for more information. $ cat postmaster.opts /usr/bin/postmaster '-D' '/var/lib/pgsql/data' It appears that the script doesn't cause the parameters in postmaster.opts to get dequoted. I think this is the source of the problem. Joe -- Joe Mitchell Knowledge Engineer Great Bridge, LLC www.greatbridge.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Bug: pg_ctl restart fails
Hi, When I try to restart postgresql, I get the following error: postgres@host $ pg_ctl restart [ok messages snipped here] /usr/local/pgsql/bin/postmaster: invalid argument -- '-D' The problem seems to be the single quotes surrounding the command line args. I confirmed this by starting postgresql, then editing postgresql.opts to remove the single quotes around '-D', then issuing the restart command as above. This time I got the following error: /usr/local/pgsql/bin/postmaster does not find the database system. Expected to find it in the PGDATA directory "'/usr/local/pgsql/data'" but unable to open file "'/usr/local/pgsql/data'/global/pg_control": No such file or directory I've tried to retype the error messages as accurately as possible. Note the single and double quotes on the last line. After, this, I used `pg_ctl start` to start the system, edited postgresql.opts to remove single quotes from around both -D and the database directory, and then attempted a restart. This time it worked like a charm. I think I've traced the problem to postgresql.c, function CreateOptsFile. When it writes to postgresql.opts, it first writes the full program name, then writes each command line argument surrounded by single quotes. It seems that it needs to either omit the single quotes altogether, or be more selective about what it quotes, like ignoring command line switches like -D while still quoting path names. If path names are left quoted, those quotes should be stripped before the path name is used to build a longer path, as per my second error. I suppose this could be done in the pc_ctl script. I would submit a patch, but the best way I know to handle string problems like this is via perl. :-) HTH. Wes Sheldahl [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] wrong dump order in pg_dump for new data types
Harald Bartel ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description wrong dump order in pg_dump for new data types Long Description Version: PostgreSQL 7.1 System: Debian Woody, both Linux 2.2.14 and 2.4.3 The problem occurs for new data types: When pg_dump is called, sometimes the CREATE TYPE is dumped before input/output functions are dumped. This makes a restore impossible. Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] grant insert for single tables does not work
Harald Bartel (harald.bartel@prozentor) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description grant insert for single tables does not work Long Description Version: PostgreSQL 7.1 System: Debian Woody, both Linux 2.2.14 and 2.4.3.1 It is not possible to grant insert for a single table. Granting this right only works if grant all is used. Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] pg_ctl restart bug
[EMAIL PROTECTED] writes: > "pg_ctl restart" fails if anything is quoted in postmaster.opts. > > $ pg_ctl restart > Smart Shutdown request at Fri Apr 20 10:11:38 2001 > postmaster successfully shut down > postmaster successfully started > /usr/bin/postmaster: invalid argument -- '-D' > Try '/usr/bin/postmaster --help' for more information. > > > $ cat postmaster.opts > /usr/bin/postmaster '-D' '/var/lib/pgsql/data' Unless someone can show me a way to extract the command line options out of the opts file while preserving whitespace in them, I'm going to eliminate the quotes being put out. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] the operator "=" does not work for some SQL queries
Harald Bartel ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description the operator "=" does not work for some SQL queries Long Description Version: PostgreSQL 7.1 System: Debian Woody, both Linux 2.2.14 and 2.4.3 For some SQL queries (for an example see below) the operator "=" on text does not work, that is, no rows are returned, although some should be returned.In PostgreSQL 7.0.3 I avoided this problem by using the LIKE operator instead of the "=" operator.In PostgreSQL 7.1 this trick does not work anymore. But I found another way to avoid the problem by using SUBSTR. For example I used substr(text1,1,8)=substr(text2,1,8) instead of text1=text2 or text1 like text2, where 8 is the length of both texts. Sample Code # original query that should work: SELECT segment.nameenglischkurz, selektioneintrag_osi(transaktionh.selektion), wp.isin, unternehmen.namekurz FROM transaktionh, segment, wp, unternehmen, wpstamm WHERE wp.osi = selektioneintrag_osi(transaktionh.selektion) and wp.isin = wpstamm.isin and wpstamm.unternehmennr = unternehmen.unternehmennr and transaktionh.segmentnr = 43 and typnr = 3 and segment.segmentnr = transaktionh.segmentnr ORDER BY verkaufdt::datetime DESC; nameenglischkurz | selektioneintrag_osi | isin | namekurz --+--+--+-- (0 rows) ## query using substr: SELECT segment.nameenglischkurz, selektioneintrag_osi(transaktionh.selektion), wp.isin, unternehmen.namekurz FROM transaktionh, segment, wp, unternehmen, wpstamm WHERE substr(wp.osi,1,8) = substr(selektioneintrag_osi(transaktionh.selektion),1,8) and wp.isin = wpstamm.isin and wpstamm.unternehmennr = unternehmen.unternehmennr and transaktionh.segmentnr = 43 and typnr = 3 and segment.segmentnr = transaktionh.segmentnr ORDER BY verkaufdt::datetime DESC; nameenglischkurz | selektioneintrag_osi | isin | namekurz --+--+--+-- DAX | DE71 | DE000710 | DaimlerChrysler DAX | DE604843 | DE0006048432 | Henkel ... No file was uploaded with this report ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] grant insert for single tables does not work
[EMAIL PROTECTED] writes: > It is not possible to grant insert for a single table. Granting this > right only works if grant all is used. Works for me: << as postgres >> regression=# create user foo; CREATE USER regression=# create table zz (f1 int); CREATE << as foo >> regression=> select * from zz; ERROR: zz: Permission denied. regression=> insert into zz values(22); ERROR: zz: Permission denied. << as postgres >> regression=# grant insert on zz to foo; CHANGE << as foo >> regression=> insert into zz values(22); INSERT 939431 1 regression=> select * from zz; ERROR: zz: Permission denied. regression=> update zz set f1 = 33; ERROR: zz: Permission denied. regression=> If you think there's a problem you're going to have to be more specific. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] grant insert for single tables does not work
> Harald Bartel (harald.bartel@prozentor) reports a bug with a severity of 3 > It is not possible to grant insert for a single table. Granting this > right only works if grant all is used. Works here. More details required. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] wrong dump order in pg_dump for new data types
[EMAIL PROTECTED] writes: > The problem occurs for new data types: > When pg_dump is called, sometimes the CREATE TYPE is dumped before > input/output functions are dumped. This makes a restore impossible. I believe this was fixed about two weeks ago. Are you sure you are using 7.1 final release, not some beta version? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] pg_ctl restart bug
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Unless someone can show me a way to extract the command line options out > of the opts file while preserving whitespace in them, I'm going to > eliminate the quotes being put out. Won't work if any of the options contain whitespace, which is quite probable. For example, my postmaster.opts currently contains /home/postgres/testversion/bin/postmaster '-i' '-o' '-F -S 5120' Removing the quotes will certainly break this. We need to find a smarter way for the script to parse the file contents. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] pg_ctl restart bug
pg_ctl restart works fine with this patch, but I'm not sure whether it breaks useful cases for the other paths: *** *** 323,335 shift po_path=$1 shift ! POSTOPTS=$@ fi else # -o given POSTOPTS="-D $PGDATA $POSTOPTS" fi ! eval '$po_path' '$POSTOPTS' $logopt '&' # if had an old lockfile, check to see if we were able to start if [ -n "$oldpid" ];then --- 323,335 shift po_path=$1 shift ! POSTOPTS="$@" fi else # -o given POSTOPTS="-D $PGDATA $POSTOPTS" fi ! eval '$po_path' $POSTOPTS $logopt '&' # if had an old lockfile, check to see if we were able to start if [ -n "$oldpid" ];then regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] the operator "=" does not work for some SQL queries
[EMAIL PROTECTED] writes: > For some SQL queries (for an example see below) the operator "=" on text does not >work, that is, no rows are returned, although some should be returned.In PostgreSQL >7.0.3 I avoided this problem by using > the LIKE operator instead of the "=" operator.In PostgreSQL 7.1 this > trick does not work anymore. But I found another way to avoid the problem by using >SUBSTR. For example I used substr(text1,1,8)=substr(text2,1,8) instead of text1=text2 >or > text1 like text2, where 8 is the length of both texts. Perhaps you are failing to consider trailing blanks in one or both input values? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] pg_ctl restart bug
> > $ cat postmaster.opts > > /usr/bin/postmaster '-D' '/var/lib/pgsql/data' > > Unless someone can show me a way to extract the command line options out > of the opts file while preserving whitespace in them, I'm going to > eliminate the quotes being put out. You know, I looked at that pg_ctl script and couldn't figure out how it properly passed all the options to the postmaster. Guess it doesn't. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] pg_ctl restart bug
I can assure you that $@ is never the way to go, always use "$@". Not sure about the second change. > pg_ctl restart works fine with this patch, but I'm not sure whether it > breaks useful cases for the other paths: > > *** > *** 323,335 > shift > po_path=$1 > shift > ! POSTOPTS=$@ > fi > else # -o given > POSTOPTS="-D $PGDATA $POSTOPTS" > fi > > ! eval '$po_path' '$POSTOPTS' $logopt '&' > > # if had an old lockfile, check to see if we were able to start > if [ -n "$oldpid" ];then > --- 323,335 > shift > po_path=$1 > shift > ! POSTOPTS="$@" > fi > else # -o given > POSTOPTS="-D $PGDATA $POSTOPTS" > fi > > ! eval '$po_path' $POSTOPTS $logopt '&' > > # if had an old lockfile, check to see if we were able to start > if [ -n "$oldpid" ];then > > > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] pg_ctl restart bug
Tom Lane writes: > pg_ctl restart works fine with this patch, but I'm not sure whether it > breaks useful cases for the other paths: Looks good. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] lo_import does not check type before performing an import
Michael Richards ([EMAIL PROTECTED]) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
lo_import does not check type before performing an import
Long Description
lo_import within pgsql does not verify that it is reading from a file. You can import
directories if you like and the imported data is a mess of ASCII. I didn't try it but
I'm sure you could get into lots of trouble if you tried something like
lo_import('/dev/urandom') or some other device that you can read infinite amounts of
data from.
This affects postgres 7.03 and possibly others.
Sample Code
urdr=# insert into test values (lo_import('/home/miker/test'));
INSERT 6816303 1
urdr=# select * from test;
t
-
6816289
(1 row)
> file /home/miker/test
/home/miker/test: directory
No file was uploaded with this report
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[BUGS] SQL function lo_unlink is not documented
Michael Richards ([EMAIL PROTECTED]) reports a bug with a severity of 4
The lower the number the more severe it is.
Short Description
SQL function lo_unlink is not documented
Long Description
http://postgresql.readysetnet.com/users-lounge/docs/7.1/programmer/lo-funcs.html
Says:
There are two built-in registered functions, lo_import and lo_export which are
convenient for use in SQL queries. Here is an example of their use
CREATE TABLE image (
nametext,
raster oid
);
INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/etc/motd'));
SELECT lo_export(image.raster, '/tmp/motd') from image
WHERE name = 'beautiful image';
This should also document the lo_unlink function as being able to remove these BLOBs
is also supported. See below.
Sample Code
urdr=# select lo_unlink(6816289);
lo_unlink
---
1
(1 row)
No file was uploaded with this report
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] lo_import does not check type before performing an import
Michael Richards ([EMAIL PROTECTED]) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
lo_import does not check type before performing an import
Long Description
lo_import within pgsql does not verify that it is reading from a file. You can import
directories if you like and the imported data is a mess of ASCII. I didn't try it but
I'm sure you could get into lots of trouble if you tried something like
lo_import('/dev/urandom') or some other device that you can read infinite amounts of
data from.
Sample Code
urdr=# insert into test values (lo_import('/home/miker/test'));
INSERT 6816303 1
urdr=# select * from test;
t
-
6816289
(1 row)
> file /home/miker/test
/home/miker/test: directory
No file was uploaded with this report
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] lo_import does not check type before performing an import
[EMAIL PROTECTED] writes: > lo_import within pgsql does not verify that it is reading from a file. So we should prohibit reading from, eg, a named pipe? Sorry, I don't agree. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
