Re: [BUGS] BUG #1715: dbmirror replaces nextval, setval functions
O James Doherty έγραψε στις Jun 10, 2005 :
>
> The following bug has been logged online:
>
> Bug reference: 1715
> Logged by: James Doherty
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.0.3
> Operating system: Solaris 8 Intel
> Description:dbmirror replaces nextval, setval functions
> Details:
>
> The dbmirror contrib program replaces some key functions when setting up the
> Mirroring table (MirrorSetup.sql). The nextval and setval functions are
> renamed as nextval_pg and setval_pg and replaced with new functions.
>
> This breaks any fields made with the SERIAL datatype, which have to be
> ALTER'ed to use the nextval_pg and setval_pg functions to work properly.
>
>
> Here's the offending code in MirrorSetup.sql:
> UPDATE pg_proc SET proname='nextval_pg' WHERE proname='nextval';
>
> CREATE FUNCTION pg_catalog.nextval(text) RETURNS int8 AS
> '$libdir/pending.so', 'nextval' LANGUAGE 'C' STRICT;
>
>
> UPDATE pg_proc set proname='setval_pg' WHERE proname='setval';
>
> CREATE FUNCTION pg_catalog.setval("unknown",integer,boolean) RETURNS int8
> AS
> '$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT;
> CREATE FUNCTION pg_catalog.setval("unknown",integer) RETURNS int8 AS
> '$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT;
You are right, since dbmirror mirrors tables based on whether
the recordchange trigger is present, whereas it seems
to mirror sequences unconditionally.
You could rename dbmirror's setval,nextval to
dbmirror_setval,dbmirror_nextval, and leave
postgresql's original functions as is.
Just a note tho:
Dbmirror is a great tool, but someone needs to hack a great deal
of code to meet his/her special requirements.
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
-Achilleus
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1703: dns for ftp mirrors messed up
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tavis Rudd > Sent: 06 June 2005 07:51 > To: pgsql-bugs@postgresql.org > Subject: [BUGS] BUG #1703: dns for ftp mirrors messed up > > > The following bug has been logged online: > > Bug reference: 1703 > Logged by: Tavis Rudd > Email address: [EMAIL PROTECTED] > PostgreSQL version: any > Operating system: any > Description:dns for ftp mirrors messed up > Details: > > none of the ftp mirrors are currently accessible do to > missing dns records. > > [EMAIL PROTECTED]: # host ftp1.us.postgresql.org > Host ftp1.us.postgresql.org not found: 3(NXDOMAIN) > > [EMAIL PROTECTED]: # dig ftp1.us.postgresql.org There aren't any mirrors numbered '1'. ftp.us and ftp3.us are both there. There isn't currently an ftp2.us. Regards, Dave ---(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] Bug#311533: Invalid timestamp returned because of timezone
Hi Tom! Tom Lane [2005-06-11 13:13 -0400]: > Martin Pitt <[EMAIL PROTECTED]> writes: > > This would require that support for both int and float timestamps is > > present in the postmaster, but wouldn't require an immediate dump and > > reload of all databases. Would that be possible in any way? > > Given infinite amounts of work, anything is possible. However, I'd rate > the chances of the above actually happening as about nil. OK, I don't have any idea how complicated this would be. > The chances of it happening in an existing stable release branch are > flat-out zero. That's reasonable and shouldn't happen. I did not intend to fix that in stable Debian/Ubuntu releases as well. :-) > I'm not sure why you are asking for this anyway: it wouldn't do > anything to solve the immediate problem for existing users. I just thought about how to provide a transition path if the long-term solution really was to get rid of integer time stamps. The proposal would have helped to be able to convert existing clusters to float timestamps without having to rely on old versions of the postmaster (which is really difficult to achieve in a packaging system). > What *would* do > something for them is to apply the patch already committed to our CVS. > > 2005-06-04 21:48 tgl > > * src/backend/commands/: variable.c (REL7_3_STABLE), variable.c > (REL7_4_STABLE), variable.c (REL8_0_STABLE), variable.c: Code for > SET/SHOW TIME ZONE with a fixed-interval timezone was not prepared > for HAVE_INT64_TIMESTAMP. Per report from Guillaume Beaudoin. Actually fixing that bug is indeed the most preferred solution. :-) Thanks a lot for this patch. I just got the impression that integer timestamps were doomed... Thanks and have a nice day, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] BUG #1712: JDBC column precision for bigint is 0
Whith the JDBC driver at least up to version 8.1dev-400, the result of the getPrecision method of ResultSetMetaData on a bigint column is 0 instead of the expected 19. This has been reported before but I haven't got to fixing it yet. This is partly because I haven't seen a good explanation of exactly what we should be returning here -- what spec says we should return 19? Well, in PostgreSQL, BIGINT uses 8 bytes (that is what the documentation says, at least). Now, with 8 bytes, the range of numbers that can be represented is: For 63 bits + 1 sign bit: [ (2^63/2)-1, -2^63/2] = [9223372036854775807, -9223372036854775808] For 64 bits (unsigned): [2^64, 0] = [18446744073709551616, 0] If you count the number of digits in these numbers, you'll notice that for the signed number, 19 decimal digits at most are required to represent it (if the sign comes for free, which seems assumed for other data types such as INT or SMALLINT). For the unsigned number, 20 decimal digits are required. But as far as I understand the PostgreSQL reference, integers are always signed, except for serial data types, but where the range is that of a signed number anyway (since they need to be compatible with "normal" integer types to represent references). This is why I believe 19 is the value the getPrecision method should return. I don't think there is some kind of standard reference that defines it, but it seems pretty clear what it should be really. I hope this convinces you, if you still have any doubts, let me know: I'll do my best to appease them ;) Sincerely, Gilles Dubochet. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #1717: using PgOleDb-1.0.0.15 in delphi i can't edit the data in control
The following bug has been logged online: Bug reference: 1717 Logged by: tiejun feng Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: linux Description:using PgOleDb-1.0.0.15 in delphi i can't edit the data in control Details: i programmed with delphi using PgOleDb-1.0.0.15 to connect postgresql database. i can get the data from my database. but i can't edit the data in the delphi's contrlo such as dbedit. why? thanks ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Bug#311533: Invalid timestamp returned because of timezone
Martin Pitt <[EMAIL PROTECTED]> writes: > I just thought about how to provide a transition path if the long-term > solution really was to get rid of integer time stamps. I don't think anyone has suggested doing that. In fact, there's a camp that wishes they'd become the default. I don't see that happening soon either, in part because it seems there are still too many bugs to be flushed out :-(. But *somebody* has to exercise the code in order to flush out the bugs, and Debian has elected to be the pioneers here. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1715: dbmirror replaces nextval, setval functions
Title: Re: [BUGS] BUG #1715: dbmirror replaces nextval, setval functions
I wouldn't say that requiring that dbmirror not break all serial
fields in the entire database is a special requirement!
The pending.so library that is compiled prior to running dbmirror would
also have to be updated, since it refers to nextval and setval as well.
As far as the actual mirroring of the database, it's working great. It's
the way that it changes the database when it is intalled that I think it is a
bug. And I could attempt to fix it with my meager knowledge of postgres and c,
but I don't think I'd be confident to submit an official fix for it.
James
-Original Message- From: Achilleus
Mantzios [mailto:[EMAIL PROTECTED] Sent: Mon 6/13/2005
2:59 AM To: James Doherty Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1715: dbmirror replaces nextval, setval
functions
O James Doherty έγραψε στις Jun 10, 2005 :>>
The following bug has been logged online:>> Bug
reference: 1715> Logged
by: James
Doherty> Email address:
[EMAIL PROTECTED]> PostgreSQL version: 8.0.3> Operating
system: Solaris 8 Intel>
Description: dbmirror replaces
nextval, setval functions> Details:>> The dbmirror
contrib program replaces some key functions when setting up the>
Mirroring table (MirrorSetup.sql). The nextval and setval functions
are> renamed as nextval_pg and setval_pg and replaced with new
functions.>> This breaks any fields made with the SERIAL
datatype, which have to be> ALTER'ed to use the nextval_pg and
setval_pg functions to work properly.>>> Here's the
offending code in MirrorSetup.sql:> UPDATE pg_proc SET
proname='nextval_pg' WHERE proname='nextval';>> CREATE FUNCTION
pg_catalog.nextval(text) RETURNS int8 AS> '$libdir/pending.so',
'nextval' LANGUAGE 'C' STRICT;>>> UPDATE pg_proc set
proname='setval_pg' WHERE proname='setval';>> CREATE FUNCTION
pg_catalog.setval("unknown",integer,boolean) RETURNS int8> AS>
'$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT;> CREATE FUNCTION
pg_catalog.setval("unknown",integer) RETURNS int8 AS>
'$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT;You are right,
since dbmirror mirrors tables based on whetherthe recordchange trigger is
present, whereas it seemsto mirror sequences unconditionally.You
could rename dbmirror's setval,nextval todbmirror_setval,dbmirror_nextval,
and leavepostgresql's original functions as is.Just a note
tho:Dbmirror is a great tool, but someone needs to hack a great dealof
code to meet his/her special
requirements.---Achilleus
Re: [BUGS] BUG #1712: JDBC column precision for bigint is 0
I have no official standing, but I've spent a lot of time reading the JDBC spec and working with various implementations, and I concur. Precision should be the maximum number of significant digits the column is capable of returning. -Kevin >>> Gilles Dubochet <[EMAIL PROTECTED]> 06/13/05 3:27 AM >>> >> Whith the JDBC driver at least up to version 8.1dev-400, the >> result of the >> getPrecision method of ResultSetMetaData on a bigint column is 0 >> instead of >> the expected 19. >> > > This has been reported before but I haven't got to fixing it yet. This > is partly because I haven't seen a good explanation of exactly what we > should be returning here -- what spec says we should return 19? > Well, in PostgreSQL, BIGINT uses 8 bytes (that is what the documentation says, at least). Now, with 8 bytes, the range of numbers that can be represented is: For 63 bits + 1 sign bit: [ (2^63/2)-1, -2^63/2] = [9223372036854775807, -9223372036854775808] For 64 bits (unsigned): [2^64, 0] = [18446744073709551616, 0] If you count the number of digits in these numbers, you'll notice that for the signed number, 19 decimal digits at most are required to represent it (if the sign comes for free, which seems assumed for other data types such as INT or SMALLINT). For the unsigned number, 20 decimal digits are required. But as far as I understand the PostgreSQL reference, integers are always signed, except for serial data types, but where the range is that of a signed number anyway (since they need to be compatible with "normal" integer types to represent references). This is why I believe 19 is the value the getPrecision method should return. I don't think there is some kind of standard reference that defines it, but it seems pretty clear what it should be really. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1712: JDBC column precision for bigint is 0
Gilles Dubochet wrote: >>> Whith the JDBC driver at least up to version 8.1dev-400, the result >>> of the >>> getPrecision method of ResultSetMetaData on a bigint column is 0 >>> instead of >>> the expected 19. >>> >> >> This has been reported before but I haven't got to fixing it yet. This >> is partly because I haven't seen a good explanation of exactly what we >> should be returning here -- what spec says we should return 19? > If you count the number of digits in these numbers, you'll notice that > for the signed number, 19 decimal digits at most are required to > represent it (if the sign comes for free, which seems assumed for other > data types such as INT or SMALLINT). [...] > This is why I believe 19 is the value the getPrecision method should > return. I don't think there is some kind of standard reference that > defines it, but it seems pretty clear what it should be really. The problem I have is how we decide that "precision" means "number of digits, ignoring sign". Actually, I just dug through the JDBC javadoc, and noticed that ResultSetMetaData.getPrecision() does talk about the number of decimal digits; but in all the other places it's used (such as DatabaseMetaData) it's not described. For numeric types using number of decimal digits seems reasonable since it's consistent with the NUMERIC type's precision/scale information. Previous reports of this also wanted to provide precision for dates and times according to the number of digits in them, which seems much more dubious. I guess it's another case of the JDBC specification being woefully underspecified :( -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1698: Different behavior in UNIQUE and DISTINCT
Yes I'm sure. I was created right before filling it. Thanks, Mauro 2005/6/3, Bruno Wolff III <[EMAIL PROTECTED]>: On Thu, Jun 02, 2005 at 20:22:07 +0100,Mauro Delfino <[EMAIL PROTECTED] > wrote:>> I have the these two tables:> CREATE TABLE table_one> ( field1 VARCHAR(255) );>> CREATE TABLE table_two> ( field1 VARCHAR(255) UNIQUE );>> table_one has 500k records. I certify that all strings are distinct with > this query:> SELECT DISTINCT field1 FROM table_one;> The query results 500k rows.>> But if I try to insert the records of table_one into table_two with the> following command: > INSERT INTO table_two (field1) (SELECT field1 FROM table_one);> This error occurs:> ERROR: duplicate key violates unique constraint "table_two_field1_key">> What happened? DISTINC and UNIQUE have different algorithms to determine > when two strings are equal?Are you sure table_two is empty when you do this?-- Mauro Delfino [EMAIL PROTECTED] (48) 9933-6933(MCDBA/MCSE/MCP+I/IBM Lotus CLS) Microsoft SQL Server Official Beta Tester
Re: [BUGS] BUG #1698: Different behavior in UNIQUE and DISTINCT
On Mon, 13 Jun 2005 10:12:38 -0300, Mauro Delfino <[EMAIL PROTECTED]> wrote: > > > I have the these two tables: > > > CREATE TABLE table_one > > > ( field1 VARCHAR(255) ); > > > > > > CREATE TABLE table_two > > > ( field1 VARCHAR(255) UNIQUE ); > > > > > > SELECT DISTINCT field1 FROM table_one; > > > The query results 500k rows. > > > > > > INSERT INTO table_two (field1) (SELECT field1 FROM table_one); > > > This error occurs: > > > ERROR: duplicate key violates unique constraint "table_two_field1_key" > > > > > > What happened? DISTINC and UNIQUE have different algorithms to determine > > > when two strings are equal? Did you forget to put distinct in your insert select? table1.field1 is not unique so it allows duplicates in table_one. Try "select field1 from table_one group by field1 having count(*) > 1" and see if it gives you any results. If you do get results, then you need to put the distinct into the insert statement. INSERT INTO table_two (field1) (SELECT distinct field1 FROM table_one); klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1707: statistics collector starts with stats_start_collector
Tom Lane wrote: > Bruce Momjian writes: > > Tom Lane wrote: > >> I don't have a problem with removing it as a writable option ... but > >> I'm thinking we should leave it as a read-only GUC parameter (like > >> the several others we have already). Otherwise we'll need to add some > >> other method of finding out whether the collector is running. > > > Why would a user care? > > luser> It's not collecting statistics! > > us> Is the collector running? > > luser> How should I know? > > us> er ... > > If we don't have a way to check this, we'll regret it soon enough... > now maybe a GUC setting isn't the optimal way, but I think we need > *some* way besides ps. ps doesn't work remotely and I think there's > no simple equivalent under Windows either. Sure, but the GUC only reports that it thinks the stats collector started, not whether it is running or not. I think 'ps' is a fine way to tell. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
