Re: [BUGS] BUG #5043: Stored procedure returning different results for same arguments
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, > please send function xfunc. Your code looks well. > please try sql function > ... > has it same behave like plpgsql function? Yes, result is the same - it returns 2008-11-01 I made further investigation - it seems the problem is here between the keyboard and the chair :-) As I wrote an answer for you I realized the problem. The xfunc() and therefore get_schemebind_date() is called from a trigger AFTER DELETE ON schemebind. So the trigger deletes the '2009-09-01' record, get_schemebind_date() then returns correct result '2008-11-01'. But xfunc() then fails and the trigger operation is rolled back and I see the '2009-09-01' record again... Sorry guys for bothering you and taking your time. Regards, - -- Martin Edlman Fortech, spol. s r.o, Ropkova 51, 57001 Litomyšl Public GPG key: http://edas.visaci.cz/#gpgkeys -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkqnfE4ACgkQqmMakYm+VJ8iswCggwTcu9pZQOaAjAIjW0D22kTs HYIAni3mLdNilwgxNeQsGFxNogBg6OCL =ZVLv -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
Re: [BUGS] BUG #5043: Stored procedure returning different results for same arguments
2009/9/9 Martin Edlman : > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hello, > >> please send function xfunc. Your code looks well. >> please try sql function >> ... >> has it same behave like plpgsql function? > > Yes, result is the same - it returns 2008-11-01 > > I made further investigation - it seems the problem is here between the > keyboard and the chair :-) > > As I wrote an answer for you I realized the problem. The xfunc() and > therefore get_schemebind_date() is called from a trigger AFTER DELETE ON > schemebind. So the trigger deletes the '2009-09-01' record, > get_schemebind_date() then returns correct result '2008-11-01'. But > xfunc() then fails and the trigger operation is rolled back and I see > the '2009-09-01' record again... > Sorry guys for bothering you and taking your time. v pohode :) Pavel > > Regards, > - -- > Martin Edlman > Fortech, spol. s r.o, > Ropkova 51, 57001 Litomyšl > Public GPG key: http://edas.visaci.cz/#gpgkeys > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkqnfE4ACgkQqmMakYm+VJ8iswCggwTcu9pZQOaAjAIjW0D22kTs > HYIAni3mLdNilwgxNeQsGFxNogBg6OCL > =ZVLv > -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
Re: [BUGS] BUG #5044: error: message type 0x5a arrived from server while idle
vyouzhi escribió:
> #!/usr/bin/env python
> #coding=utf-8
> import _pg
> import threading
>
> con =
> _pg.connect(host='127.0.0.1',user='work',dbname='work',passwd='123',port=543
> 2)
>
> def link_url():
> global con
> for i in range(100):
> print i
> stock_sql = "INSERT INTO code (stock_name
> ,stock_code)values('b','b')"
> try:
> con.query(stock_sql)
> except:
> print stock_sql
>
> if __name__=='__main__':
> for i in range(0,32):
> print i
> i = threading.Thread(target = link_url)
> i.start()
>
> ---
Not a bug. Using a connection from more than one thread is not allowed.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
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] BUG #5045: java developer
The following bug has been logged online: Bug reference: 5045 Logged by: mahmoud badr Email address: alien_...@yahoo.com PostgreSQL version: 8.4 Operating system: windows Description:java developer Details: when trying to create POJOs from Netbeans IDE they aren't ceated when the DBMS is postgreSQL and they are created if the DBMS is any thing else. please correct me if i'm wrong. -- 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 #5045: java developer
On Wed, Sep 9, 2009 at 5:41 AM, mahmoud badr wrote: > > The following bug has been logged online: > > Bug reference: 5045 > Logged by: mahmoud badr > Email address: alien_...@yahoo.com > PostgreSQL version: 8.4 > Operating system: windows > Description:java developer > Details: > > when trying to create POJOs from Netbeans IDE they aren't ceated when the > DBMS is postgreSQL and they are created if the DBMS is any thing else. > please correct me if i'm wrong. > > Is there an error of any kind? I have successfully done this many times without issue. > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [BUGS] BUG #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
For those of you who are still looking at this, I tried to reproduce the issue
by holding one of the WAL files open with another program (just opened it with
the cygwin build of less.exe for windows). That didn't do the trick. It
prevented unlink or rename from working at all. I wrote a program (open.exe)
that opens the file using pgwin32_open() and passed in the same parameters that
postgres uses when opening a WAL file. That allowed the file to be renamed.
And, when deleted, the open file went into the pending deletion state.
I used open.exe to hold onto a WAL file that was going to be recycled. The
recycling worked, but what is going to happen down the road when the handle is
released, leaving a gap in the WAL file sequence. Or if it is not released,
when a backend tries to open the WAL file and does not have access to it?
When open.exe was holding onto a WAL file that was going to be deleted, the
deletion worked. The file went into the deletion pending state. The archive
status for the WAL file went through the .ready ==> .done ==> {no status file}
==> .ready sequence. At that point Postgres repeatedly tries to archive the
WAL file.
I reported earlier that I believe postgres leaked the file handle to the WAL
file. I don't believe that is the case. We have a process that only checks
data in the database for integrity. It is only reading. I think it opened the
WAL file initially, perhaps the backend had some maintenance work to do when
that session started and had to write something to the WAL and then never moved
on to a new one.
Now that I can reproduce the pending deletion case, I'm working on code to
detect it reliably and, hopefully, efficiently.
-Luke
> -Original Message-
> From: pgsql-bugs-ow...@postgresql.org
> [mailto:pgsql-bugs-ow...@postgresql.org] On Behalf Of Luke Koops
> Sent: Monday, September 07, 2009 4:30 PM
> To: 'Tom Lane'; Heikki Linnakangas
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #5038: WAL file is pending deletion
> in pg_xlog folder, this interferes with WAL archiving.
>
> > -Original Message-
> > From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> > Sent: Monday, September 07, 2009 1:17 PM
> > To: Heikki Linnakangas
> > Cc: Luke Koops; pgsql-bugs@postgresql.org
> > Subject: Re: [BUGS] BUG #5038: WAL file is pending deletion
> in pg_xlog
> > folder, this interferes with WAL archiving.
> >
> > Heikki Linnakangas writes:
> > > Perhaps we should try to close the old WAL file sooner.
> >
> > There is zero hope of making that work. What we probably
> need to do
> > is fix the code that scans pg_xlog so that it ignores files
> that are
> > pending deletion. (I assume there's some way to find that out on
> > Windows.)
> On *nux systems, unlink removes the link from the directory.
> When other processes get a directory listing, the file will
> no longer be listed. On Windows, the file name continues to
> show up in directory listings. The file is in a state called
> pending deletion. Windows documentation doesn't give a
> specific test for this state. Perhaps you could use _access().
>
> From http://support.microsoft.com/kb/159199
>
> This file is in a state known as pending deletion. This file
> has been deleted, but there are still handles open to it.
> NTFS will wait until all handles to this file are closed
> before updating the index. If an attempt is made to access
> the file, however, NTFS will deny the attempt. Because the
> file is listed in the index, but is effectively deleted, you
> can see the file but you cannot access it.
>
> Windows NT returns an "Access Denied" error message when you
> attempt to manipulate the file. You are not able to view the
> permissions, the owner, or the contents of the file. The file
> does, however, show up in a DIR listing in File Manager and
> in Explorer. This occurs even though the user trying to
> access the file has permissions to the file. Even an
> administrator will be unable to take ownership of this file.
>
> >
> > 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
>
--
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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
Luke Koops wrote:
> For those of you who are still looking at this, I tried to reproduce the
> issue by holding one of the WAL files open with another program (just opened
> it with the cygwin build of less.exe for windows). That didn't do the trick.
> It prevented unlink or rename from working at all. I wrote a program
> (open.exe) that opens the file using pgwin32_open() and passed in the same
> parameters that postgres uses when opening a WAL file. That allowed the file
> to be renamed. And, when deleted, the open file went into the pending
> deletion state.
Yeah, it's the FILE_SHARE_DELETE flag that allows the deletion.
> I used open.exe to hold onto a WAL file that was going to be recycled. The
> recycling worked, but what is going to happen down the road when the handle
> is released, leaving a gap in the WAL file sequence. Or if it is not
> released, when a backend tries to open the WAL file and does not have access
> to it?
When the file is recycled, I believe we're fine. The file is not
deleted, only renamed, so it won't be deleted when open.exe closes it.
No gap in WAL sequence is created.
> When open.exe was holding onto a WAL file that was going to be deleted, the
> deletion worked. The file went into the deletion pending state. The archive
> status for the WAL file went through the .ready ==> .done ==> {no status
> file} ==> .ready sequence. At that point Postgres repeatedly tries to
> archive the WAL file.
> I reported earlier that I believe postgres leaked the file handle to the WAL
> file. I don't believe that is the case. We have a process that only checks
> data in the database for integrity. It is only reading. I think it opened
> the WAL file initially, perhaps the backend had some maintenance work to do
> when that session started and had to write something to the WAL and then
> never moved on to a new one.
>
> Now that I can reproduce the pending deletion case, I'm working on code to
> detect it reliably and, hopefully, efficiently.
I got hold of a Windows virtual machine as well, and could reproduce the
issue. It was a bit tricky to coerce the file to be deleted instead of
recycled, but setting "max_advance = 0" in RemoveOldXlogFiles() finally
did the trick.
I googled around, and saw some discussion that suggest that when a file
is in "pending deletion" state, it's implemented by setting a
"delete-on-close" flag on the existing file handle. The upshot of that
is that if you pull the power plug, the file won't be deleted after all.
One option is to rename the file before deleting it. For all practical
purposes, that's the same as if the file no longer exists. Seems like
the simplest solution to me.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
Heikki Linnakangas writes: > One option is to rename the file before deleting it. I was under the impression that renaming would fail in the same cases where deletion fails, ie something else holding the file open ... 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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
Tom, If you first delete the file and it goes into deletion pending, then subsequent attempts to rename or delete will fail. Heikki is proposing that the file be renamed before it is first deleted. -Luke > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Wednesday, September 09, 2009 3:06 PM > To: Heikki Linnakangas > Cc: Luke Koops; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5038: WAL file is pending deletion > in pg_xlog folder, this interferes with WAL archiving. > > Heikki Linnakangas writes: > > One option is to rename the file before deleting it. > > I was under the impression that renaming would fail in the > same cases where deletion fails, ie something else holding > the file open ... > > 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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
Luke Koops writes: > Heikki is proposing that the file be renamed before it is first > deleted. Indeed, and what I'm saying is that that will still fail if some unhelpful program is holding the file open without FILE_SHARE_DELETE. Which is what I understood your case to be ... 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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
My case is that a backend was holding the file open. It was not some other unhelpful program. That's why I had to write a program that held the file open with the same flags that postgres uses (including FILE_SHARE_DELETE). We have a process that wrote to the DB, and then for many days it only read from the DB, so it didn't move on to another WAL file. -Luke > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Wednesday, September 09, 2009 3:20 PM > To: Luke Koops > Cc: Heikki Linnakangas; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5038: WAL file is pending deletion > in pg_xlog folder, this interferes with WAL archiving. > > Luke Koops writes: > > Heikki is proposing that the file be renamed before > it is first deleted. > > Indeed, and what I'm saying is that that will still fail if > some unhelpful program is holding the file open without > FILE_SHARE_DELETE. > Which is what I understood your case to be ... > > 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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
Tom Lane wrote: > Luke Koops writes: >> Heikki is proposing that the file be renamed before it is first >> deleted. > > Indeed, and what I'm saying is that that will still fail if some > unhelpful program is holding the file open without FILE_SHARE_DELETE. > Which is what I understood your case to be ... No, it's a backend that's holding the file open, with FILE_SHARE_DELETE. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
Heikki Linnakangas writes: > No, it's a backend that's holding the file open, with FILE_SHARE_DELETE. If that's the only case we care about covering, then rename might be enough. I was just wondering what it would take to solve the more general problem of something holding it open with the wrong flags at the time we want to get rid of 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
[BUGS] need higher extra_float_digits value (3)
I have applications that require IEEE floating-point data to remain unchanged to the last bit after a dump/restore cycle. Currently on all our linux systems, pg_dump uses 8 significant figures (FLT_DIG + 2) to dump 32-bit reals and 17 significant figures (DBL_DIG + 2) to dump 64-bit doubles, where FLT_DIG and DBL_DIG are defined in float.h as 6 and 15, respectively, and 2 is the maximum allowed value for extra_float_digits. While 17 decimal digits are sufficient to guarantee the complete recovery of all 64-bit double values, some 32-bit reals actually require NINE significant figures. For example, the binary real 0x3dcccd70 is converted using 8 significant figures to 0.1122, which if converted back to binary will become 0x3dcccd71. A general proof for why 9 significant figures are required for 32-bit reals (and 17 for 64-bit doubles) is in Theorem 15 of David Goldberg's "What Every Computer Scientist Should Know About Floating-Point Arithmetic", which is available on the web and as an appendix to Sun's Numerical Computation Guide. I have been running a locally modified postgresql server with extra_float_digits' upper limit increased by one to 3 and a modified pg_dump which sets extra_float_digits to 3; I have verified that the modification does what I need and it does not seem to have any side effect other than providing one more significant figure than needed for double precision binary to decimal conversion. I would like to ask the postgresql development team to consider it for a future release. Thanks, Keh-Cheng Chu Solar Physics Group, Hansen Experimental Physics Lab Stanford University P.S. I run slony with extra_float_digits set to 3 in (my locally modified) postgresql.conf in order to get around the same precision problem. It would be nice if slony would do something similar to pg_dump and enable the extra digits by default; I will bring that up with slony developers if this modification is accepted. -- 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] need higher extra_float_digits value (3)
Keh-Cheng Chu writes: > While 17 decimal digits are sufficient to guarantee the > complete recovery of all 64-bit double values, some 32-bit > reals actually require NINE significant figures. Hm, annoying. Seems like we could do one of two things: 1. Increase the max extra_float_digits value to 3 and have pg_dump use that. 2. Split extra_float_digits into two settings so that float4 and float8 can use different settings. Offhand the only argument I can see for #2 is that #1 might bloat dump files unnecessarily --- but it's not likely to be significant on a percentage basis. We'd also need to check the code to be sure it's leaving room for the extra digit in all cases, though I doubt there's really any problem. Comments? 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] need higher extra_float_digits value (3)
On Wed, Sep 9, 2009 at 6:04 PM, Tom Lane wrote: > Keh-Cheng Chu writes: >> While 17 decimal digits are sufficient to guarantee the >> complete recovery of all 64-bit double values, some 32-bit >> reals actually require NINE significant figures. > > Hm, annoying. Seems like we could do one of two things: > > 1. Increase the max extra_float_digits value to 3 and have pg_dump use > that. > > 2. Split extra_float_digits into two settings so that float4 and float8 > can use different settings. > > Offhand the only argument I can see for #2 is that #1 might bloat dump > files unnecessarily --- but it's not likely to be significant on a > percentage basis. > > We'd also need to check the code to be sure it's leaving room for the > extra digit in all cases, though I doubt there's really any problem. (2) seems like overkill to me, FWIW. ...Robert -- 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 #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving.
Tom Lane wrote: > Heikki Linnakangas writes: >> No, it's a backend that's holding the file open, with FILE_SHARE_DELETE. > > If that's the only case we care about covering, then rename might be > enough. I was just wondering what it would take to solve the more > general problem of something holding it open with the wrong flags > at the time we want to get rid of it. Yes, that's a separate problem, and I think we should address that too. That's what I thought was going on in OP's case at first, the patch I posted in my first reply should address that. I'll try to reproduce that case too, and verify that the patch fixes it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
