[BUGS] Unlisten / listen in a transaction failure
I came across some unusual behavior with listen. Basically, if you unlisten and listen inside of a transaction, new notices are not picked up right away - but they will show up if you send yourself a notice. It also works as expected if you unlisten, commit, and then re-listen. Tested on 9.1 and 9.2. Demo psql script: listen abc; \t \! psql -p 5491 -c 'notify abc' select * from pg_listening_channels(); begin; unlisten *; listen abc; commit; \! psql -p 5491 -c 'notify abc' select * from pg_listening_channels(); notify abc; Output of above on 9.1 with psql -e: listen abc; LISTEN Showing only tuples. NOTIFY select * from pg_listening_channels(); abc Asynchronous notification "abc" received from server process with PID 10879. begin; BEGIN unlisten *; UNLISTEN listen abc; LISTEN commit; COMMIT NOTIFY select * from pg_listening_channels(); abc notify abc; NOTIFY Asynchronous notification "abc" received from server process with PID 10882. Asynchronous notification "abc" received from server process with PID 10876. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 signature.asc Description: Digital signature
Re: [BUGS] Prepared Statement Name Truncation
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Gavin Flower asks: > Would it be appropriate to make it a WARNING in 9.2.2, then > increase the length in 9.3? No: revisions are reserved for bug fixes. This would be more of a behavior fix and as such would go into a major version. Gavan Schneider wrote: > (Wild speculation) There may be a "sweet spot" using even shorter > identifiers than is the case now, with full disambiguation, which > might improve overall performance. I really don't think the length is really a bottleneck, but others can correct me if it is. Tom Lane wrote: > There's some possible value in having a non-default option to throw > error for overlength names, but TBH I fear that it won't buy all that > much, because people won't think to turn it on when testing. > > Given the historical volume of complaints (to wit, none up to now), > I can't get very excited about changing the behavior here. I think > we're more likely to annoy users than accomplish anything useful. Well, as with many other things, a lack of complaints does not indicate there is no problem. I've certainly seen this problem in the wild before, but have not bothered to file an official bug report or anything. Perhaps my bad, but the problem is out there. How would you feel about switching from NOTICE to WARNING, Tom? That seems to make a lot more sense as we are changing the user's input, which warrants more than a notice IMO. Separately, what are the objections to raising the size limit to 128? - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201211211525 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlCtOYMACgkQvJuQZxSWSsjmEQCfb6GOEs7jwst1ao70L+j8IW5q gNYAn110QAhwjuhUSW3/uexvU+StsfZz =iw6q -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] Prepared Statement Name Truncation
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > NOTICE: identifier > "this_is_a_really_long_identifier_for_a_prepared_statement_name_ok" > will be truncated to > "this_is_a_really_long_identifier_for_a_prepared_statement_name_" > PREPARE ... > The ORM could use a shorter identifier, but it supports multiple backends > and this is probably not something in their test suite. In addition it > actually works! For now. If it really works, then by definition it does not /need/ to be that long, as the truncated version is not blowing things up. > So I am sharing this with the list to see what people think. Is this a > configuration bug? An ORM bug? A postgres bug? An unfortunate > interaction? Part ORM fault, part Postgres. We really should be throwing something stronger than a NOTICE on such a radical change to what the user asked for. I'd lobby for WARNING instead of ERROR, but either way, one could argue that applications would be more likely to notice and fix themselves if it was stronger than a NOTICE. > If it's a postgres bug, what is the fix? Make the identifier max size > longer? I'd also be in favor of this, in addition to upgrading from a NOTICE. We no longer have any technical reason to keep it NAMEDATALEN, with the listen/notify rewrite, correct? If so, I'd like to see the max bumped to at least 128 to match the default SQL spec length for similar items. > Set a hard limit and ERROR instead of truncating and NOTICE? > Both? Neither because that would break backward compatibility? My vote is WARNING and bump limit to 128 in 9.3. That's the combo most likely to make dumb applications work better while not breaking existing smart ones. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201211172246 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlCoWpYACgkQvJuQZxSWSsi4NwCfQfq7NEQ3xiLpPZLsu0I9iGT4 pOAAmgPEsm2iYCPiVfzMEM2EX2nihQE9 =wLpM -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 #6707: ERROR: could not open relation with OID
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Hmm. The problem with this is that the DROP TABLE invalidates test's rowtype, so on a second call the already-compiled "ROW(NULL)::test" expression refers to a type that no longer exists. > We could fix that by adding datatypes to the set of objects that are > tracked to determine when a cached plan must be invalidated, but I'm > more than a bit hesitant to expend the extra cycles for this, given > the lack of previous complaints and the large number of datatypes > that are mentioned in most plans. re previous complaints: I've run across this in the past but just chalked it up to a "guess Postgres doesn't like it done that way". I suspect there may be others in the same boat? > Perhaps it would be good enough to only track types that must be > rowtypes (ie, are mentioned in RowExpr or ConvertRowtypeExpr)? +1 - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201207012259 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk/xDoYACgkQvJuQZxSWSsiFJwCfQBqt/SUkFHeA8FP+mq3nKtW6 GFwAnjx7WVlCCws7XdsQYDOVYzzpC8iW =q158 -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] Failed pgbench: setrandom invalid maximum number 0
> setrandom: invalid maximum number 0 Okay, nevermind, user error: some other process was deleting a row from pgbench_branches, and the pgbench.c happily set scale to 0 from the 'select count(*) from pgbench_branches' query inside of it, until it gives the totally not-helpful error seen above later on as it sets :scale to 0, multiplies the tpc_b default number of branches by zero, and then complains when the minimum number of branches (1) is less than the max allowed (0). Oddly enough, the code checks for the count(*) < 0 but not <= 0. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpsWYz5znX55.pgp Description: PGP signature
Re: [BUGS] Failed pgbench: setrandom invalid maximum number 0
> > but the problem seems to only occur in >= 8.4. But it's also Good news and bad news. The good news is that it started occuring on all versions of pgbench, which makes it more likely to be a problem with my system rather than pgbench (although that error message sure is inscrutable). The bad news is I had to reboot the box for other reasons and I cannot duplicate the issue. Yet. :) -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpDnyhGQdLj3.pgp Description: PGP signature
[BUGS] Unlogged tables cannot be truncated twice
Wow, this one took a bit to narrow down. Here's the failing case: # create unlogged table foo (a text); CREATE TABLE # begin; BEGIN #* truncate table foo; TRUNCATE TABLE #* truncate table foo; ERROR: could not create file "base/19131/19183_init": File exists Very reproducible. The column types matter: if the only column is an INT, for example, the problem does not occur. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgp7FCBOAEQDc.pgp Description: PGP signature
Re: [BUGS] Failed pgbench: setrandom invalid maximum number 0
> That's not valid syntax. Some versions of getopt() take it upon > themselves to rearrange the switch order, some do not ... Sorry: I should have made clear this happens without the -n as well, I just wanted to make the sample output a little smaller. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpw1DgcGtmAQ.pgp Description: PGP signature
[BUGS] Failed pgbench: setrandom invalid maximum number 0
Seeing the following on an old box I use for testing various
things. Thought it was something to do with my box at first,
but the problem seems to only occur in >= 8.4. But it's also
not pgbench alone, as it was working fine one day, and not the
next. Here's what I now get on 8.4, 9.0, and 9.1:
$ pgbench btest1 -n
setrandom: invalid maximum number 0
Client 0 aborted in state 3. Execution meta-command failed.
transaction type: TPC-B (sort of)
scaling factor: 0
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 0/10
tps = 0.00 (including connections establishing)
tps = 0.00 (excluding connections establishing)
What I get on 8.3 and older is this:
$ pgbench btest1 -n
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 332.005312 (including connections establishing)
tps = 372.703216 (excluding connections establishing)
Here's a snippet of strace for the failing run:
<>
munmap(0xb773b000, 4096)= 0
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl64(3, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
fcntl64(3, F_SETFD, FD_CLOEXEC) = 0
connect(3, {sa_family=AF_FILE, path="/tmp/.s.PGSQL.5900"}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [0], [4]) = 0
getsockname(3, {sa_family=AF_FILE, NULL}, [2]) = 0
time(NULL) = 1306638084
time(NULL) = 1306638084
poll([{fd=3, events=POLLOUT|POLLERR}], 1, 1) = 1 ([{fd=3, revents=POLLOUT}])
send(3, "\0\0\0#\0\3\0\0user\0greg\0database\0btest"..., 35, MSG_NOSIGNAL) = 35
time(NULL) = 1306638084
poll([{fd=3, events=POLLIN|POLLERR}], 1, 1) = 1 ([{fd=3, revents=POLLIN}])
recv(3, "R\0\0\0\10\0\0\0\0S\0\0\0\26application_name\0\0"..., 16384, 0) = 332
gettimeofday({1306638084, 217401}, NULL) = 0
write(2, "setrandom: invalid maximum numbe"..., 36setrandom: invalid maximum
number 0
) = 36
write(2, "Client 0 aborted in state 3. Exe"..., 60Client 0 aborted in state 3.
Execution meta-command failed.
) = 60
<>
--
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
pgpDcL0wQEaVM.pgp
Description: PGP signature
Re: [BUGS] 9.1 plperlu bug with null rows in trigger hash
On Mon, May 23, 2011 at 05:04:40PM -0600, Alex Hunsaker wrote: ... > Greg, can you confirm the attached fixes it for you? Yes, seems to have done the job, thank you. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpmugDD5ToZ2.pgp Description: PGP signature
[BUGS] 9.1 plperlu bug with null rows in trigger hash
I've not been able to duplicate this in a standalone script yet,
but in the guts of Bucardo is a trigger function called validate_goat()
that is giving this error on 9.1 HEAD, but not on previous versions:
"Failed to add table "public.pgbench_tellers": DBD::Pg::st execute
failed: ERROR: Modification of non-creatable hash value attempted,
subscript "pkey" at line 4."
I was able to simplify the function to just this and still produce
the error:
CREATE OR REPLACE FUNCTION bucardo.validate_goat()
RETURNS TRIGGER
LANGUAGE plperlu
AS
$bc$
my $new = $_TD->{new};
$new->{pkey} = 'foobar';
return 'MODIFY';
$bc$;
It's used like this:
CREATE TRIGGER validate_goat
BEFORE INSERT OR UPDATE ON bucardo.goat
FOR EACH ROW EXECUTE PROCEDURE bucardo.validate_goat();
The goat table has many text fields, of which one is
pkey. Setting it to any of those other columns will cause the error.
However, setting it to a text field that is NOT NULL DEFAULT will
*not* produce the error, so obviously something is setting
$_TD->{new}{somecol} to undef in the wrong way. I'm baffled as
to why I cannot reproduce it standalone, but wanted to get the
bug out there so I don't forget about it and in case anyone
wants to take a swing at it. Some Googling suggests it might
be because we are using &PL_sv_undef instead of a proper
newSV(0).
--
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
pgpNfLiacvvId.pgp
Description: PGP signature
Re: [BUGS] Plperl trigger variables no longer global
On Mon, May 16, 2011 at 12:57:41PM -0600, Alex Hunsaker wrote: > > Do we need to apply this patch? ... > My proposed fix is instead of declaring $_TD in Yes, please apply, I'm eager to continue testing 9.1 but cannot proceed until something is in place. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpxsdPUmFHlK.pgp Description: PGP signature
[BUGS] Plperl trigger variables no longer global
This works in 9.0 but not in git/9.1 beta:
CREATE FUNCTION wheredidmytdgo()
RETURNS TRIGGER
LANGUAGE plperlu
AS
$bc$
use strict; use warnings;
my $new = $_TD->{new};
return;
$bc$;
The error is:
ERROR: Global symbol "$_TD" requires explicit package name at line 3.
CONTEXT: compilation of PL/Perl function "wheredidmytdgo"
--
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
pgpx7IJuLn2As.pgp
Description: PGP signature
Re: [BUGS] [Fwd: DBD::Pg on HP-UX 11.31 64bit]
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 (yes, I'm going through old emails tonight) > Spinning off into the off-topic based on the question - if you do > this, please make it *optional*. Unless you plan to actually implement > all the libpq functionality and also shoulder the burden to release a > new version whenever a new version of libpq is out. Things like > kerberos/gssapi and cert authentication that actually work the same > way as others... Er, no, it certainly will not be optional, that kind of ruins the point. But yes, we will certainly not remove any functionality and keep up with any libpq changes. Not sure why you would think a new version would be released when a new version of libpq is released - currently we are linked to any old random libpq that happens to be on the user's box, and it almost always is not the latest one. :) Once this is in place, I expect we'll be making changes that will be picked up *by* libpq, because it frankly hasn't seen much love lately. > Might be worth looking at what the ODBC folks did though - they run > the actual protocol for the queries, but they use libipq for > connection setup and authentication (if found - they'll fallback on > doing simple auth etc if libpq isn't there) Yeah, we might end up doing something like that, but it seems easier from this distance to simply subsume a good copy of libpq into the DBD::Pg tree. Thanks for the feedback and idea. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102082203 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1SBEEACgkQvJuQZxSWSsiZcwCg7nRBwTnQ9bmVUMPXtk3cZShV 70sAnijn2nbWaRVMgljnKz4mqZDqVklk =m89q -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 #5802: configure script does not check for perl devel files
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> However, during the make, postgres requires perl devel. Should this >> not be covered in the configure script? > > No, it's not configure's job to do the job of make or the compiler. Nonsense, we should be checking everything we can. Why wait until a future step fails if we can rule it out as early as possible? We already do that for a number of other things. - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201012291437 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk0bjfMACgkQvJuQZxSWSsh6OACgpnMGAIdNXMljmzqV1ur18cV2 4RQAoNHPioGbelSpd9Dcm55bgkwqmZq7 =gy/W -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] [Fwd: DBD::Pg on HP-UX 11.31 64bit]
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > How much are the postgres people willing to find a solution that would > force postgres to optional use BSD type sockets? > FWIW, my long-term plan is to have DBD::Pg provide its own libpq. Not sure if this would enable you to solve the problem or not, but I suspect it might. Actually removing the libpq dependency is awaiting copious amounts of free time on my part, or funding from a sponsor. But it's on the todo list. :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201012190741 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk0N/Z0ACgkQvJuQZxSWSsjXtwCg6wb/Z+ws/3Rzs1rwkwjW8Awt CSAAoLsyz0atsNS3Hi+9f16NlxAC1dtE =GqaK -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] Invalid YAML output from EXPLAIN
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane wrote: ... > Egad ... this is supposed to be an easily machine-generatable format? > > If it's really as broken as the above suggests, I think we should > rip it out while we still can. Heh ... not like you to shrink from a challenge. ;) I don't think the above would be particularly hard to implement myself, but if it becomes a really big deal, we can certainly punt by simply quoting anything containing an indicator (the special characters above). It will still be 100% valid YAML, just with some excess quoting for the very rare case when a value contains one of the special characters. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201006071035 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkwNA+4ACgkQvJuQZxSWSshSswCg81kd3FdYnQup1eLWGesm+vm+ VO8AoL1Fwil/vXfRdRHx4A4zZUTDbZuT =oPDv -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] Invalid YAML output from EXPLAIN
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Dean Rasheed wrote:
...
> So the current code in escape_yaml() is inadequate for producing valid
> YAML. I think it would have to also consider at least the following
> characters as special "-" ":" "[" "]" "{" "}" "," "\"" "'"
> "|" "*" "&". Technically, it would also need to trap empty strings,
> and strings with leading or trailing whitespace.
>
> Making escape_yaml() completely bulletproof with this approach would
> be quite difficult, and (IMO) not worth the effort
...
Doesn't seem like a lot of effort to me. You've already laid out most of
the exceptions above, although they require a few tweaks.
The rules should be:
Requires quoting only if the first character:
& * ! | > ' " % @ ` #
Same as above, but no quoting if the second character is "safe":
- ? :
Always requires quoting:
":" "#" aka ': ' ' #'
Always requires quoting:
, [ ] { }
Always require quoting:
(leading space) (trailing space) (empty string)
See:
http://yaml.org/spec/1.2/spec.html section 5.3 and 7.3.3
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201006070943
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAkwM+wAACgkQvJuQZxSWSsgWZACcCgb0rDvA6ZVhHId/q568gBGo
sjgAoLY7HbkI7sRpO45vi0jSRJ2Fiytk
=v7T/
-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] psql or pgbouncer bug?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> The auto-reconnect behavior is long-established and desirable. What's >> not desirable is continuing with any statements remaining on the same >> line, I think. We need to flush the input buffer on reconnect. > So if I understand it correctly, if I need correct transaction behaviour > in psql even in case of disconnection the only safe way is to use one > statement per line. > > Is this correct? Yes, that is correct. Pretty big gotcha. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005240925 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkv6fncACgkQvJuQZxSWSsipbQCg3Cn6Hh4Uk9i2TwaKNgzB1Xef apIAoLiNoJT4pjtA4xaZXL11XdgUYwph =MF9l -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 report (#5456) not showing up on the ML
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > 2 days ago I made a bug report through the bug reporting form on > postgresql.org regarding some unusual bloat in my TOAST tables, but it still > hasn't shown up on this ML, so I guess it's stuck in the moderation queue. > Could someone look at it (and hopefully approve it so it ends up here)? I > believe it was #5456, but am not 100% sure (bad memory and didn't save the > number). There is nothing in the queue except for some spam (zapped!), so you probably need to send it again. I'll keep a close eye on the queue today in case it gets stuck there. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005121404 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEUEAREDAAYFAkvq7acACgkQvJuQZxSWSshbMACYxihPHv0ZtnSRI2s9XCVTpmGB +QCg5uYAzPAW/3DWSifgjba6nj3SOgg= =49LV -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] bugs that have not been replied-to on list
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > That all sounds pretty reasonable to me, though I would favor using > something other than Bugzilla for the tracker. I'm not really sure if > there's anything that I'd consider truly good out there, but I've > always found Bugzilla pretty terrible. Bugzilla is the worst form of bug tracking out there, except for all the others. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201004181546 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkvLYZUACgkQvJuQZxSWSsh/LACfWCELkWETKE5PVOBWcKF/EhKj ahoAoNzBtdhiFFoRyrLVn7+KLEwMwE7L =eP30 -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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Well that's the thing, probably by what I described below that. Namely > get something working for 9.1 and after we know its good and solid see > if we can back patch it. Just don't break anything in 9.0 that relies on plperl please. :) To that end, let me know when HEAD has something somewhat stable, and I'll run some tests against it (e.g. Bucardo, which uses lots of plperl) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201002251458 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkuG1qIACgkQvJuQZxSWSshX4gCgrEPDLc5GQFKF0zf0eEZv4wDv Qt8AoOngHlVD+OXs26naSnqcrJl4xGFG =Ec6y -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
[BUGS] Invalid explain output for multi-plan statements
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
The new explain formats break if you have a multi-query statement.
I don't have time to fix at the moment, but I'll try and explain
the problem. For YAML, the forced leading space in all output
means that the first "- Plan:" has two spaces, and all other ones
have a single space. This leads to an inconsistent indentation
error when parsing. For JSON, I'm not sure where the exact problem
lies, but it also will not parse as it produces something like this:
[
{
...plan 1
},,
{
...plan2
}
]
The XML output looks valid, but I've not tried to parse it.
To duplicate:
CREATE TABLE abc(a int);
INSERT INTO abc VALUES (1);
CREATE TABLE def(a int);
CREATE RULE foo AS ON UPDATE TO abc DO ALSO SELECT 1 FROM def;
EXPLAIN (format YAML) UPDATE abc SET a=a;
EXPLAIN (format JSON) UPDATE abc SET a=a;
EXPLAIN (format XML) UPDATE abc SET a=a;
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200912141230
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAksmdisACgkQvJuQZxSWSsjaZACfeErCQbAU3a4DK3WqNBCaQMPI
oe8AoKDsP+bIvsV2e2qD/Jx1NhrQw4ui
=5lky
-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 #5087: Submitted bug reports not showing up in a timely manner (or at all)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Anyway the important point here is whether there's any significant > number of posts that get lost for a reason other than being rejected > as spam. Only the moderators would have any idea how many get rejected > as spam, so I'd still like to hear from one of them (or somebody with > access to the moderation data anyway). I'm sure there are legitimate posts that get swept up in the spam cleaning. The bugs list, like most others, is hammered with spam. Very little of the email to -bugs comes from the web form (and those that do are very seldom themselves spam. 10% or less sounds about right. - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 200909291817 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkrCh6UACgkQvJuQZxSWSsg6IACglYUQvA3ERen0Zw7OxytAsxL0 K6QAmwTFusEDEyreSMYEqO8LF3Qpzby0 =LG/T -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] [HACKERS] Cannot use all four trigger events at once
erOneEvent
{
! char *e = palloc(4);
e[0] = $1; e[1] = $3; e[2] = $5; e[3] =
'\0';
$$ = e;
}
;
TriggerOneEvent:
--- 3171,3198
TriggerEvents:
TriggerOneEvent
{
! char *e = palloc(5);
e[0] = $1; e[1] = '\0';
$$ = e;
}
| TriggerOneEvent OR TriggerOneEvent
{
! char *e = palloc(5);
e[0] = $1; e[1] = $3; e[2] = '\0';
$$ = e;
}
| TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent
{
! char *e = palloc(5);
e[0] = $1; e[1] = $3; e[2] = $5; e[3] =
'\0';
$$ = e;
}
+ | TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent
OR TriggerOneEvent
+ {
+ char *e = palloc(5);
+ e[0] = $1; e[1] = $3; e[2] = $5; e[3] =
$7; e[4] = '\0';
+ $$ = e;
+ }
;
TriggerOneEvent:
Index: src/include/nodes/parsenodes.h
===
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.394
diff -c -r1.394 parsenodes.h
*** src/include/nodes/parsenodes.h 11 Jun 2009 14:49:11 - 1.394
--- src/include/nodes/parsenodes.h 18 Jun 2009 00:44:15 -
***
*** 1551,1557
List *args; /* list of (T_String) Values or
NIL */
boolbefore; /* BEFORE/AFTER */
boolrow;/* ROW/STATEMENT */
! charactions[4]; /* 1 to 3 of 'i', 'u', 'd', +
trailing \0 */
/* The following are used for referential */
/* integrity constraint triggers */
--- 1551,1557
List *args; /* list of (T_String) Values or
NIL */
boolbefore; /* BEFORE/AFTER */
boolrow; /* ROW/STATEMENT */
! charactions[5];
[BUGS] Cannot use all four trigger events at once
This was failing:
CREATE TRIGGER foo
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON foobar
FOR EACH STATEMENT EXECUTE PROCEDURE baz();
Turns out the parser wasn't set up to handle four different trigger
event types. Patch attached.
--
Greg Sabino Mullane g...@endpoint.com g...@turnstep.com
End Point Corporation 610-983-9073
PGP Key: 0x14964AC8 200906171620
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Index: backend/parser/gram.y
===
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.664
diff -r2.664 gram.y
3174c3174
< char *e = palloc(4);
---
> char *e = palloc(2);
3180c3180
< char *e = palloc(4);
---
> char *e = palloc(3);
3189a3190,3195
> | TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent
> {
> char *e = palloc(5);
> e[0] = $1; e[1] = $3; e[2] = $5; e[3] = $7; e[4] = '\0';
> $$ = e;
> }
signature.asc
Description: OpenPGP digital signature
Re: [BUGS] BUG #4736: Mediawiki by Postgres
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I get an error since the upgrade version of mediawiki 1.13 to 1.14 > 2009-03-26 14:03:49 CET ERREUR: la relation category n'existe pas It looks as though you did not run "php update.php" from the maintenance directory, per the upgrade directions. If you don't have shell access, it's also possible to do this by re-running the web-based installer. See: http://www.mediawiki.org/wiki/Manual:Upgrading_to_1.14#Run_the_update_script If the problem persists, posting something to bugzilla.mediawiki.org is probably a better place to raise a bug. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200903261000 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAknLin4ACgkQvJuQZxSWSsixxgCg2Ds9uozVQoJvTwGRuDadKCaF ZKYAoJIWnoRHBnVESOZVlcvQKrPgBNxf =epC0 -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 #4410: Indexes not seen right away
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > > > Does it have pg_index.indcheckxmin = true? If so, see README.HOT. > >> Yes, that was probably it. Is this worth noting in the documentation >> somewhere >> (other than the technical bowels of HOT)? Perhaps in the CREATE INDEX >> docs? ... > I have attached the scripts I used for testing. I don't think it is > worth documenting this until we have 8.4 released and people start using > it. I'm not following this, Bruce. HOT came out in 8.3, not 8.4, so why would we wait on documenting the problem? > I believe a mention in the manual would require quite complex wording. Seems something would be better than nothing. Example: "Note: In versions 8.3 and higher, indexes may not be immediately visible due to the way HOT chains work. For more information, please see ." It might be nice to upgrade README.HOT to a real documentation page as well. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200810051758 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkjpOKkACgkQvJuQZxSWSshJ/QCgzeTdyP7tEZnYc+4GZZhyTPJ2 8IQAoJDmkbBDdGYlUoF7W3pcJSNvInW6 =aStx -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 #4410: Indexes not seen right away
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Does it have pg_index.indcheckxmin = true? If so, see README.HOT. Yes, that was probably it. Is this worth noting in the documentation somewhere (other than the technical bowels of HOT)? Perhaps in the CREATE INDEX docs? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200809081638 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkjFjVwACgkQvJuQZxSWSsglCACg18kkSFnwXYgf6LXxV/UC98Us jU8An3zMxd58t3A5NemfbHJ++uANYkUu =rHHT -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
[BUGS] BUG #4410: Indexes not seen right away
The following bug has been logged online: Bug reference: 4410 Logged by: Greg Sabino Mullane Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3 Operating system: Linux Description:Indexes not seen right away Details: I cannot reproduce, as this was on a production system and not seen again, but I created a simple index on a TEXT field, which was not chosen by the planner, even when seqscan was turned off. I analyzed the table, checked all the settings, etc. pg_index looked as it should. Eventually (~ 10 minutes later) the index as chosen - I don't know what might have triggered it to start appearing. Creating a second table based on the first worked as it should during the 'noindex' time period: CREATE TABLE foo AS SELECT * FROM bar; CREATE INDEX foo_idx1 ON foo(textcol); EXPLAIN SELECT 1 FROM foo WHERE textcol1 = 'baz'; -- 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] 8.3.0 backend segfaults
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Work load is a web application where each page beings a transaction; > creates a temp table, does a few selects, inserts and updates and the > commits. Are you sure you are calling DBI->connect *after* the Apache children are created? Major problems like this can happen if not. The use of prepare_cached() may be adding to the problem as well, especially if you are using temp tables. In DBD::Pg, prepared statements are not actually prepared (in most cases) until just before the first execute, to account for late bindings and to be more efficient. Some related DBD::Pg attribs to look at are pg_server_prepare and pg_prepare_now. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803121121 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfX9Q0ACgkQvJuQZxSWSsjEjACg6QNUdPIw5gczfTtFK3aUMh39 fUYAoLwRrFZ75z2Fbq7GDRYqgTlRsR9N =ngbh -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] Planner problems in 8.2.4 and 8.2.5 (was: Possible planner bug/regression introduced in 8.2.5)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane replied: > If there are 100 or more histogram entries it'll do the estimation by > counting how many of the histogram entries match the pattern, rather > than using the prefix-range-based estimator (which is pretty much > all-fantasy anyway for a pattern with leading % :-(). > > http://archives.postgresql.org/pgsql-committers/2006-09/msg00331.php Ugh, that's some gotcha. Now that the patches are in place to fix the planner problems, can I strongly recommend that a 8.2.6 version be made? These are some serious planner problems, reported by real-world users, and the only other option may be to go back to 8.1. > I'm too lazy to go check, but I think the stats target is interpreted as > the number of histogram bins rather than values, which is why you'd see > the switchover at 99 not 100. Can we switch the default_statistics_target to a default of 100? Is there any reason not to do so at this point? Ten has always seemed very low to me, and the 98/99 gotcha only makes the case for at least 100 as the default stronger. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200711132325 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHOnkKvJuQZxSWSsgRA9i/AJ4rN3BANdWKLmrscVpij0GiZ1i/lwCg4u4x 6U+7bTe2o60Kv44f+6n61Zc= =MSBf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Planner problems in 8.2.4 and 8.2.5 (was: Possible planner bug/regression introduced in 8.2.5)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Is there a reason you rounded off most of the costs? It looks like the > estimated costs of the two join types are nearly equal, and so it's pure > chance which one gets chosen. No real reason, it's just a post-processing script used to make explain output a little more readable. I'll leave in all the sigfigs next time. > This might be a bug in the LIKE estimator (if so, it's in 8.2.3 as > well). I don't have time to look closer right now, but can you show us > the pg_stats row for orders_smaller.order_number? I tried the patch you sent, with no change. However, I then changed the default_statistics_target to 100, reanalyzed, and it came back with the "good" plan. Trying this on the original larger query (which pulls from tables with millions of rows, not the 10,000 subsets I created) worked fine too. Very odd. I confirmed that a lower stats worked fine on 8.2.3, and then narrowed it down to 99 - at 98 and below on 8.2.5, the "bad" plan is used, and at 99 and above, the "good" one is. I guess as a rule of thumb we'll crank up the default estimate on our 8.2 boxes. Any particular number recommended? Any reason why 99 is so magical? I could have sworn I tried it with 100 last week and saw the bad plan. Guess I should also boost my default target testing up a bit as well. Thanks for the quick patch, we'll definitely apply that as well for safety. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200711081137 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHMztMvJuQZxSWSsgRA0pfAKDHWvUafv0bwL/nzmP5yXuptTPX7gCfbMNr uMLI9yy6Prwt0DOHBsLu/Pk= =1Vsj -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] Planner problems in 8.2.4 and 8.2.5 (was: Possible planner bug/regression introduced in 8.2.5)
I don't have a full test case yet, but I did finally manage to get an
explain analyze to finish in a sane amount of time on 8.2.5. Attached
are two cleaned up explain analyze results, using the exact same data
directory but different executables: one is 8.2.3 and returns as
expected, the other is 8.2.5, which generates a slow plan despite any
fiddling with geqo/join_collapse_limit, etc. The cost is the same, but
it makes a wrong turn partway through the plan. This 8.2.5 has the
earlier patches from Tom already posted applied to it (also tested on
8.2.4 and 8.2.5 with the same result). An earlier version of the query
with tables truncated to 10 rows ran in 70 seconds on 8.2.3, and did
not finish after an hour on 8.2.5. This version has the tables truncated
to a mere 1 rows each. The query itself is a view calling some large
views, which call other views and functions, etc. I can post a version
of it if needed, but my energy is mostly focused now on making a
reproducible test case. Now that this is locally reproducible in a
finite amount of time, patches and tweaking suggestions are welcome.
(Postgres built with no special flags from source, all tables have been
analyzed, '***' in the explain analyze plans indicates places manually
made things more readable).
Run on a 8.2.3 server:
Aggregate (C=6832..6832 R=1) (AT=1665..1665 R=1 L=1)
->Hash Join (C=5007..6832 R=50) (AT=1543..1657 R=9340 L=1)
Hash Cond: ("substring"((a.order_number)::text, '^[^.]+'::text) =
(a.base_order)::text)
->GroupAggregate (C=2519..4194 R=1) (AT=101..158 R=1 L=1)
->Sort (C=2519..2544 R=1) (AT=101..109 R=10001 L=1)
Sort Key: o.order_number, (** many other "o" columns)
->Merge Left Join (C=0..1855 R=1) (AT=0..61 R=10001 L=1)
Merge Cond: ((o.order_number)::text = (ol.order_number)::text)
->Index Scan using orders_smaller_i1 on orders_smaller o (C=0..1147
R=1) (AT=0..12 R=1 L=1)
->Index Scan using orderlines_smaller_i4 on orderlines_smaller ol
(C=0..577 R=8440) (AT=0..10 R=8435 L=1)
->Hash (C=2487..2487 R=1) (AT=1442..1442 R=9247 L=1)
->Merge Join (cost=2487.46..2487.49 rows=1 width=36) (actual
time=1376.130..1431.848 rows=9247 loops=1)
Merge Cond: ("outer"."?column2?" = "inner"."?column2?")
->Sort (C=1405..1405 R=1) (AT=1186..1193 R=9247 L=1)
Sort Key: (a.base_order)::text
->Subquery Scan a (cost=1405.91..1405.94 rows=1 width=18) (actual
time=1112.757..1135.560 rows=9247 loops=1)
->HashAggregate (C=1405..1405 R=1) (AT=1112..1121 R=9247 L=1)
->Nested Loop Left Join (C=18..1405 R=5) (AT=0..1095 R=9248 L=1)
->Nested Loop Left Join (C=18..1404 R=5) (AT=0..1017 R=9247 L=1)
Join Filter: ((o.order_number)::text ~ (('^'::text ||
(m.order_number)::text) || '(\\.[0-9.]+)?$'::text))
->Seq Scan on orders_smaller m (C=0..742 R=1) (AT=0..11 R=9247
L=1)
Filter: ((order_number)::text !~~ '%.%'::text)
->Bitmap Heap Scan on orders_smaller o (C=18..634 R=)
(AT=0..0 R=1 L=9247)
Recheck Cond: (((o.order_number)::text >=
(m.order_number)::text)
AND ((o.order_number)::text <=
((m.order_number)::text || '/'::text)))
Filter: (((status)::text <> 'split'::text) AND
((status)::text <> 'canceled'::text)
AND ((status)::text <> 'incomplete'::text) AND
((status)::text <> 'fraud'::text))
->Bitmap Index Scan on orders_smaller_i1 (C=0..17 R=)
(AT=0..0 R=1 L=9247)
Index Cond: (((o.order_number)::text >=
(m.order_number)::text)
AND ((o.order_number)::text <=
((m.order_number)::text || '/'::text)))
->Index Scan using orderlines_smaller_i4 on orderlines_smaller
ol (C=0..0 R=1) (AT=0..0 R=0 L=9247)
Index Cond: ((o.order_number)::text = (ol.order_number)::text)
->Sort (C=1081..1081 R=1) (AT=189..196 R=9247 L=1)
Sort Key: (b.base_order)::text
->Subquery Scan b (cost=1081.46..1081.50 rows=1 width=18) (actual
time=116.554..139.130 rows=9247 loops=1)
->HashAggregate (C=1081..1081 R=1) (AT=116..125 R=9247 L=1)
->Nested Loop Left Join (C=17..1081 R=3) (AT=0..101 R=9247 L=1)
Join Filter: ((p.order_number)::text ~ (('^'::text ||
(m.order_number)::text) || '(\\.[0-9.]+)?$'::text))
->Seq Scan on orders_smaller m (C=0..742 R=1) (AT=0..10 R=9247
L=1)
Filter: ((order_number)::text !~~ '%.%'::text)
->Bitmap Heap Scan on payments_smaller p (C=17..311 R=)
(AT=0..0 R=0 L=9247)
Recheck Cond: (((p.order_number)::text >=
(m.order_number)::text)
AND ((p.order_number)::text <=
((m.order_number)::text || '/'::text)))
Filter: *** ((transaction_type IN ('A','B',
Re: [BUGS] Possible planner bug/regression introduced in 8.2.5
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Tom Lane wrote: >> Please try the attached patch (in addition to the one I sent earlier). > This is biting us too, quite badly. Any chance this can get pushed into a > 8.2.6? > Those patches are certainly already in the 8.2 CVS branch, so your > question seems to mean "are we going to push 8.2.6 immediately to fix > this". My vote would be no --- 8.2.5 is less than six weeks old and > we don't have that many bugs against it. Given the overhead involved > in a release, both from our point of view as packagers and users' point > of view in having to install it, a single bug has to be pretty darn > catastrophic to force an update by itself. This doesn't seem to me > to reach that level... I suppose catastophic is in the eye of the beholder, but this is very, very severe to one of our clients. So much so that they may end up going back to 8.1. The patches to 8.2.5 fix some of the queries, but not all; some of the problems seem to exist on 8.2.4 as well. We'll try to develop a self-contained test case that shows the problem, but until then wanted to give a heads up that a problem may still exist. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200711051303 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHL1rpvJuQZxSWSsgRAzNuAJ9xkww4QwjoavHO9SkTf9Zm7Jl9PgCfQ77k UXPer7AeI0xXe/f3XkMkUps= =imfW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] Possible planner bug/regression introduced in 8.2.5
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane wrote: > Please try the attached patch (in addition to the one I sent earlier). This is biting us too, quite badly. Any chance this can get pushed into a 8.2.6? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200710291212 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHJgZyvJuQZxSWSsgRA61KAJ9ZIz220HZD8u2fr0T+NEg+rFh/AwCgmZIA iXhO5S+j3jGyqxamsQxqYlY= =pR+g -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Deferred FK / PK deletion problems
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane wrote: >> Came across an odd bug while dealing with deferred foreign keys. > I'm not convinced this is a bug. Can you elaborate on this? Am I doing something wrong in my app? Someone on irc pointed out that this affects more than deferred fk, but for my purposes, here's what's happening: Table A has a primary key. Table B references that primary key. Process A periodically updates the table by doing (basically) a delete all/insert new data, inside of a transaction. Process B is adding entries to table B. If Process B happens in the "middle" of Process A, the insert to B fails as it claims that the corresponding row in table A does not exist. Short of Process A grabbing an exclusive lock on the table, I can't see a way around this. Feel free to punt this to general if this is the expected behavior. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200710151809 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHE+VUvJuQZxSWSsgRAzyGAKCveD8q0a8O2XFEkD1g5f08Z58mbgCgvHUF z4bBO7MJ0gWow1fPHJY09is= =ohAQ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] Deferred FK / PK deletion problems
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Came across an odd bug while dealing with deferred foreign keys.
Short story: messing around with the PK table screws up deferred
constraints on the FK table. Here's a quick script to demonstrate.
Confirmed as broken on today's cvs version, as well as on 8.2 and
8.1:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
use Time::HiRes qw/gettimeofday tv_interval/;
my $t0 = [gettimeofday];
my $type = 'M';
my $port = 5432;
my $dbh1 = DBI->connect("dbi:Pg:dbname=greg;port=$port",'greg','',
{AutoCommit=>0,PrintError=>0,RaiseError=>0});
my $dbh2 = DBI->connect("dbi:Pg:dbname=greg;port=$port",'greg','',
{AutoCommit=>0,PrintError=>0,RaiseError=>0});
$dbh1->{InactiveDestroy} = 1; $dbh2->{InactiveDestroy} = 1;
$dbh1->do("DROP TABLE bar; DROP TABLE foo");
$dbh1->commit();
$dbh1->{RaiseError}=1; $dbh2->{RaiseError}=1;
$dbh1->do(qq{
CREATE TABLE foo (
foo_id INT NOT NULL PRIMARY KEY
);
CREATE TABLE bar (
bar_id INT NOT NULL PRIMARY KEY,
foo_id INT NOT NULL
);
ALTER TABLE bar ADD CONSTRAINT bar_ref_foo FOREIGN KEY (foo_id)
REFERENCES foo(foo_id) DEFERRABLE INITIALLY DEFERRED;
});
$dbh1->commit();
go(1, "INSERT INTO foo VALUES (1)");
go(1, "INSERT INTO bar VALUES (1,1)");
go(1, "COMMIT");
go(1, "DELETE FROM foo");
if (fork) {
$type = 'F';
go(2, "INSERT INTO bar VALUES (3,1);");
go(2, "COMMIT");
exit;
}
sleep 1;
go(1, "INSERT INTO foo VALUES (1)");
go(1, "COMMIT");
my $run = 1;
sub go {
my ($db,$com) = @_;
$run++;
printf "DB $db [%0.3f] {$type$run} RUN: $com\n", tv_interval($t0);
my $dbh = $db==1 ? $dbh1 : $dbh2;
my $res;
eval {
$res = $com eq 'COMMIT' ? $dbh->commit() :
$com =~ /^SELECT/ ? $dbh->selectall_arrayref($com) : $dbh->do($com);
};
if ($@) {
chomp $@;
printf "DB $db [%0.3f] {$type$run} ERROR: [EMAIL PROTECTED]",
tv_interval($t0);
$dbh->rollback();
}
}
__DATA__
Output:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for
table "foo"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for
table "bar"
DB 1 [0.120] {M1} RUN: INSERT INTO foo VALUES (1)
DB 1 [0.124] {M2} RUN: INSERT INTO bar VALUES (1,1)
DB 1 [0.126] {M3} RUN: COMMIT
DB 1 [0.162] {M4} RUN: DELETE FROM foo
DB 2 [0.165] {F5} RUN: INSERT INTO bar VALUES (3,1);
DB 2 [0.170] {F6} RUN: COMMIT
DB 1 [1.168] {M5} RUN: INSERT INTO foo VALUES (1)
DB 1 [1.169] {M6} RUN: COMMIT
DB 2 [1.183] {F6} ERROR: DBD::Pg::db commit failed: ERROR: insert or update on
table "bar" violates foreign key constraint "bar_ref_foo"
DETAIL: Key (foo_id)=(1) is not present in table "foo".
- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200710111804
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFHDp4IvJuQZxSWSsgRAz9RAKD0HzqNlVrcM5/m+IZY5+D4W2ZfsgCgyXt8
sqioJN8iHhIo+RQWcH3p3E8=
=YbhB
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [BUGS] "Relation not found" error but table exits.
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > This also ties into the discussions we've had off-and-on about making > catalog lookups behave in an MVCC fashion instead of using SnapshotNow. > I'm still pretty hesitant to go there, but maybe we could do something > involving MVCC for unlocked lookups and then SnapshotNow for (re)reading > a table's schema info once we've got lock on it. No ideas, but a strong +1 for making catalog lookups MVCC. Can this perhaps become a TODO so we don't forget about it and possibly entice people to give it a go? :) - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200703262326 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFGCI8ZvJuQZxSWSsgRA71vAKCNHCRtQUhxVoYKiSmxUAohFSE6TgCeN5qt sdb4PWjhBn+6sepNPTWkArQ= =18qw -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3116: attribute has wrong type
The following bug has been logged online: Bug reference: 3116 Logged by: Greg Sabino Mullane Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.8 Operating system: Linux Description:attribute has wrong type Details: Reproduced on 8.2.3, 8.1.8, and CVS HEAD: CREATE OR REPLACE FUNCTION gregtest(int) RETURNS varchar LANGUAGE sql AS $$ SELECT 'abc'::text; $$; -- Works: SELECT gregtest(123); -- Does not: SELECT boom FROM ( SELECT 123, gregtest(123) AS boom) AS tmp; ERROR: attribute 2 has wrong type DETAIL: TABLE has type text, but query expects character varying. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2683: spi_exec_query in plperl returns column names which are not marked as UTF8
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane asked: > Well, we're still supporting some OS versions that are way over five > years old. ISTM the real question is what do we buy if we make such > a restriction? Getting rid of a few small ifdefs doesn't seem like > an adequate reason. Is there some major improvement we could make? Well, as you just pointed out in the last commit, Unicode-aware hashes. Unicode in general was cleaned up and overhauled in 5.8, so if pl/perl is going to make a serious attempt to support it, it probably should require 5.8. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200610151657 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFMqG1vJuQZxSWSsgRApmpAJ9B29AhaBGnEA6h7o5FgemlrIUgzgCgtTZu QZkaGYy0iH0JnHoZGoE/KRE= =hgIs -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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 #2683: spi_exec_query in plperl returns column names which are not marked as UTF8
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > #if PERL_BCDVERSION >= 0x5006000L ... > #endif ... > I'm tempted to consolidate this into a function on the order of > newSVstring(const char *) or some such. Comments? +1 I suggested at one point raising the minimum requirement for Perl to 5.6 (which came out way back in 2000, so we're unlikely to upset anyone). If we haven't done that already, this would be a good chance as we can get rid of that ugly #if block. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200610151328 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFMnA9vJuQZxSWSsgRAoS8AKDxCVgCLggaDH+d1BbcEROZORqhEwCg6qe+ wrVsJMi+EKGvnmVGK4MroaM= =Oi3J -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Partial index causing wrong results to be returned on 8.1.3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > If it gives right answers after you increase work_mem sufficiently, > I'd bet on this one ... That's a good bet. Changing it from 1024 to 3500 did the trick ... only one row returned, even when using the index. Thanks very much! - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200608290100 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFE88nuvJuQZxSWSsgRAkA8AJ9xKgOcEmK4swC0Dji4WtEs4p+juwCeIHJN xyNAvlAT5c2O6pWI0C2G03I= =EvGR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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] Partial index causing wrong results to be returned on 8.1.3
I could not duplicate this on any identical database, but thought I would report it here, just for completeness. I've tried reindexing, vacuuming, etc. Explain plans all look normal. Version is 8.1.3 \d bigtable ... "bigtable_status_partial" btree (status) WHERE status::text <> 'shipped'::text greg=> select (select count(*) from bigtable where status = 'shipped') + (select count(*) from bigtable where status <> 'shipped') UNION select count(*) from bigtable; ?column? -- 1185213 1600569 greg=> drop index bigtable_status_partial; greg=> select (select count(*) from bigtable where status = 'shipped') + (select count(*) from bigtable where status <> 'shipped') UNION select count(*) from bigtable; ?column? -- 1185213 (1 row) -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200608290003 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 signature.asc Description: This is a digitally signed message part
Re: [BUGS] use-after-free in psql
> There's a minor bug in the ON_ERROR_ROLLBACK code in psql. In
> HEAD, at line 878 the storage pointed to by "results" is
> released by a PQclear(), but is referenced by the
> PQcmdStatus() calls on lines 898, 899, and 900.
>
> I'm busy at the moment -- if someone wants to fix this
> (backport to 8.1 please!), have at it.
Attached is a quick patch for HEAD and 8.1, which should do the job.
Thanks for finding this.
--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200606301039
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Index: common.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.119
diff -u -u -r1.119 common.c
--- common.c 14 Jun 2006 16:49:02 - 1.119
+++ common.c 30 Jun 2006 13:33:31 -
@@ -875,8 +875,6 @@
if (OK)
OK = PrintQueryResults(results);
- PQclear(results);
-
/* If we made a temporary savepoint, possibly release/rollback */
if (on_error_rollback_savepoint)
{
@@ -884,23 +882,35 @@
/* We always rollback on an error */
if (transaction_status == PQTRANS_INERROR)
+ {
+ PQclear(results);
results = PQexec(pset.db, "ROLLBACK TO pg_psql_temporary_savepoint");
+ }
/* If they are no longer in a transaction, then do nothing */
else if (transaction_status != PQTRANS_INTRANS)
+ {
+ PQclear(results);
results = NULL;
+ }
else
{
/*
- * Do nothing if they are messing with savepoints themselves: If
+ * Do nothing if they are messing with savepoints themselves: if
* the user did RELEASE or ROLLBACK, our savepoint is gone. If
* they issued a SAVEPOINT, releasing ours would remove theirs.
*/
if (strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 ||
strcmp(PQcmdStatus(results), "RELEASE") == 0 ||
strcmp(PQcmdStatus(results), "ROLLBACK") == 0)
+ {
+PQclear(results);
results = NULL;
+ }
else
+ {
+PQclear(results);
results = PQexec(pset.db, "RELEASE pg_psql_temporary_savepoint");
+ }
}
if (PQresultStatus(results) != PGRES_COMMAND_OK)
{
@@ -909,8 +919,8 @@
ResetCancelConn();
return false;
}
- PQclear(results);
}
+ PQclear(results);
/* Possible microtiming output */
if (OK && pset.timing)
Index: common.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.110.2.1
diff -u -u -r1.110.2.1 common.c
--- common.c 22 Nov 2005 18:23:27 - 1.110.2.1
+++ common.c 30 Jun 2006 13:42:29 -
@@ -1067,8 +1067,6 @@
if (OK)
OK = PrintQueryResults(results);
- PQclear(results);
-
/* If we made a temporary savepoint, possibly release/rollback */
if (on_error_rollback_savepoint)
{
@@ -1076,23 +1074,35 @@
/* We always rollback on an error */
if (transaction_status == PQTRANS_INERROR)
+ {
+ PQclear(results);
results = PQexec(pset.db, "ROLLBACK TO pg_psql_temporary_savepoint");
+ }
/* If they are no longer in a transaction, then do nothing */
else if (transaction_status != PQTRANS_INTRANS)
+ {
+ PQclear(results);
results = NULL;
+ }
else
{
/*
- * Do nothing if they are messing with savepoints themselves: If
+ * Do nothing if they are messing with savepoints themselves: if
* the user did RELEASE or ROLLBACK, our savepoint is gone. If
* they issued a SAVEPOINT, releasing ours would remove theirs.
*/
if (strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 ||
strcmp(PQcmdStatus(results), "RELEASE") == 0 ||
strcmp(PQcmdStatus(results), "ROLLBACK") == 0)
+ {
+PQclear(results);
results = NULL;
+ }
else
+ {
+PQclear(results);
results = PQexec(pset.db, "RELEASE pg_psql_temporary_savepoint");
+ }
}
if (PQresultStatus(results) != PGRES_COMMAND_OK)
{
@@ -1101,8 +,8 @@
ResetCancelConn();
return false;
}
- PQclear(results);
}
+ PQclear(results);
/* Possible microtiming output */
if (OK && pset.timing)
signature.asc
Description: This is a digitally signed message part
Re: [BUGS] updating unique columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 | UPDATE | orderby SET orderby = orderby +10 | WHERE groupid = 1; | -> FAILS because of UNIQUE INDEX One workaround is to do it in two steps, assuming that orderby is > 0 for all rows you are changing: BEGIN; UPDATE mytable SET orderby = -orderby WHERE groupid = 1; UPDATE mytable SET orderby = -orderby+10 WHERE groupid = 1; COMMIT; | UPDATE | orderby SET orderby = orderby +10 | WHERE groupid IN | ( SELECT groupid | FROM TABLE WHERE group_id = 1 | ORDER BY orderby DESC ) No idea what you are trying to do here: try posting the actual SQL used. However, an ORDER BY has no meaning inside of a subselect passed to IN, as IN treats the list as bag of values, and does not care about the internal order. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200606011030 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEfvpFvJuQZxSWSsgRAjQlAKDTNIpwbSEk0gcQp2pI7LokG+qwWgCgt/b6 /7ZWYDb4gufE4b0zCHyFZgg= =4LQ8 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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] BUG #1950: Subroutine info cached in pl/perl
The following bug has been logged online:
Bug reference: 1950
Logged by: Greg Sabino Mullane
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0 and cvs
Operating system: Linux
Description:Subroutine info cached in pl/perl
Details:
Inner subroutines seem to be caching initial values (e.g. the all important
%_TD hash)
\o /dev/null
CREATE TEMP TABLE event_problem (a int);
CREATE OR REPLACE FUNCTION event_problem() RETURNS TRIGGER LANGUAGE plperlu
AS
$$
my $event = $_TD->{event};
elog(INFO, "Top event: $event");
my $newname = $_TD->{new}{a};
elog(INFO, "Top newname : $newname");
&subber($event);
sub subber {
my $arg = shift;
elog(INFO, join " | " => caller(0));
elog(INFO, join " | " => caller(1));
elog(INFO, "Sub info : $info");
elog(INFO, "Sub global : $event");
elog(INFO, "Sub direct : $_TD->{event}");
my $newname = $_TD->{new}{a};
elog(INFO, "Sub newname : $newname");
}
elog(INFO, "Bottom event : $event");
return;
$$;
CREATE TRIGGER event_problem BEFORE INSERT ON event_problem
FOR EACH ROW EXECUTE PROCEDURE event_problem();
CREATE TRIGGER event_problem2 BEFORE UPDATE ON event_problem
FOR EACH ROW EXECUTE PROCEDURE event_problem();
-- Also happens with a single BEFORE UPDATE OR INSERT
\o
INSERT INTO event_problem(a) VALUES (22);
UPDATE event_problem SET a = 33;
INSERT INTO event_problem(a) VALUES (44);
UPDATE event_problem SET a = 55;
Outputs:
INFO: Top event: INSERT
INFO: Top newname : 22
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : INSERT
INSERT 0 1
INFO: Top event: UPDATE
INFO: Top newname : 33
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : UPDATE
UPDATE 1
INFO: Top event: INSERT
INFO: Top newname : 44
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : INSERT
INSERT 0 1
INFO: Top event: UPDATE
INFO: Top newname : 55
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : UPDATE
INFO: Top event: UPDATE
INFO: Top newname : 55
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : UPDATE
UPDATE 2
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] PSQL commands not backwards-compatible
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: >>> This isn't a bug, it's a feature request. We've never had backwards >>> compatibility of psql backslash commands. >> In the past, most of these usually worked. > They've been broken on a fairly regular basis in past releases. > Certainly 7.3 broke every single one because of the addition of > schema syntax ... I think that example is the exception, and not the rule. All other versions /except/ 7.3 worked for the most part with older versions. I think this is a worthy goal. We don't have to try and go back to 7.2 or anything, but I think most users would presume that psql would at least work on at least the previous version. If it's the beta timing, I can understand that, but there wasn't all this fuss when I made psql backwards-compatible last time for tablespaces. Also, I would think the no new feature rule could be bent a little here, considering that psql is a relatively small standalone app, and, as pointed out, we are definitely going to see people complain about this on the list. A little bit of effort now will make the project look much better in the long run. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508301952 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDFPHQvJuQZxSWSsgRAgtaAKDbRUWHlrqbtIipJiJjP2TaX9OesQCg2IBe cUDudmot1bIZpWqhtVH/7OA= =E6aE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1831: plperl gives error after reconnect.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Tom Lane once mentioned that "Valgrind is fairly useless for debugging > postgres," but has anybody tried it for this problem? I tried using > the FreeBSD port but it's having trouble (first I had to hack in > support for a system call, now it's terminating the postmaster with > SIBGUS on a call to setproctitle). I've got valgrind working, but not sure exactly how to use it to debug this problem. What's the procedure? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508190955 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMF5N8ACgkQvJuQZxSWSsi6eQCggFJT5i9phqGomACJk/ZIKDgS vv8AnROppubywG9bY2ZU26MMfG3lKPdj =+srT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1831: plperl gives error after reconnect.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane asked: > I could not duplicate this in either 8.0 or HEAD branches. It looks > a bit like an old bug that we had in plperl, though. Are you sure your > plperl.so is up to date? Looks like Michael is already far along, but yes, my plperl.so was up to date. This is on a Red Hat Linux box, using --with-perl and --with-gnu-ld as the only compile options. It's a very subtle bug: on my box, simply leaving out the trigger definition, or having the function not do a spi_exec_query will not raise the error. I've worked around this locally by not using plperlu (hence the original reason to switch to another user), but I sure miss being able to do "use strict" :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508181050 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMEoFkACgkQvJuQZxSWSsjTpwCgmt9kLApba6xDygvgl5qb/vdc Zh4AoPx1or9LLWSTUZQDcDjxJCfNBb08 =5Jt7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #1831: plperl gives error after reconnect.
The following bug has been logged online:
Bug reference: 1831
Logged by: Greg Sabino Mullane
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system: Linux
Description:plperl gives error after reconnect.
Details:
Tested on 8.0.1 and in current cvs. This only happens if all the steps below
are followed, including the reconnect.
\c postgres
CREATE TABLE g (name TEXT);
CREATE OR REPLACE FUNCTION testone() RETURNS text LANGUAGE plperl AS
$$
spi_exec_query(qq{INSERT INTO g(name) VALUES ('abc')});
return "ok";
$$;
CREATE OR REPLACE FUNCTION enamer() RETURNS TRIGGER LANGUAGE plperl AS
$$
return;
$$;
CREATE TRIGGER trigtest BEFORE INSERT ON g FOR EACH ROW EXECUTE PROCEDURE
enamer();
\c postgres
select testone();
ERROR: error from Perl function: creation of Perl function failed:
(in cleanup) Undefined subroutine &main::mksafefunc called at (eval 4) line
2. at (eval 4) line 2.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[BUGS] FWD: bug report: index is not a btree
The following bug has been logged online: Bug reference: 1802 Logged by: Jenny Wang Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.10 Operating system: RedHat 8 Description:index is not a btree Details: 1 $ cd $ cd bin 2 $ ./postmaster -D data & $ ./psql TEST TEST=#create table a(col1 int primary key); 3 $ kill -9 4 $ ./postmaster -D data & $ ./psql TEST TEST=#insert into a values(1); ERROR: Index a_pkey is not a btree the file of a_pkey has size 8k, and is all zero. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] Background writer process terminating
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: >Signal 9 almost has to be the OOM killer. You sure there's nothing >about "Out of Memory" in /var/log/messages? There is nothing, but I certainly can believe in the OOM scenario. The problem has not happened again since I tweaked the httpd settings, so I'll assume that was it until I see it again. Thanks, - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503290723 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCSUkwvJuQZxSWSsgRAjoFAJ9gk3WSh6K4asgk3JBgAaQNMrhvdgCgwALz lrZlKbATcg//odNHOUz3TkA= =BSgg -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Background writer process terminating
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Well, it seems to be saying that it was terminated by SIGKILL which I > can't see a reason to be internally generated. Is there anything else > running on the system that might (for example) be taxing memory to cause > an OOM killing spree or some such? Yes, that is certainly possible. I've got some mod_perl kids that could potentially flare up when the site gets busy. I don't think that explains the one at 3:30 in the morning though, but I've reduced the number of kids and I'll see if that helps. So far, no more occurances since 16:15 EST. Thanks, - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503282200 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCSMUPvJuQZxSWSsgRAqwnAJ4lGO4ZBboE9xCbva6Wtk2b+MPOaACgxrA9 AoKz1mGOsf/L8pAy1+ybM4E= =zYYP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Background writer process terminating
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Could the situation have been going on longer than just today? How > far back do your logs go? What, if anything, has changed on the > system since the last time you're certain this problem wasn't > happening? Logs go back to February 22, when 8.0.1 was put in place. No major changes lately that I can think of. Started today at 3:38 am. There is a nightly cron job that fires at 3:37 to vacuum and is undoubtably related, but the others do not seem to correspond to anything in particular (there are always users hitting the db via web scripts of course). > Is there any pattern to "periodically"? Not many data points yet, but here's all the occurances: $ grep "signal 9" 5810.log <2005-03-28 03:38:14 EST >LOG: server process (PID 29216) was terminated by signal 9 <2005-03-28 10:15:45 EST >LOG: background writer process (PID 29271) was terminated by signal 9 <2005-03-28 11:05:00 EST >LOG: background writer process (PID 2202) was terminated by signal 9 <2005-03-28 12:22:26 EST >LOG: background writer process (PID 7748) was terminated by signal 9 <2005-03-28 12:27:37 EST >LOG: background writer process (PID 17119) was terminated by signal 9 <2005-03-28 12:27:48 EST >LOG: background writer process (PID 17404) was terminated by signal 9 <2005-03-28 12:28:27 EST >LOG: background writer process (PID 17409) was terminated by signal 9 <2005-03-28 12:28:41 EST >LOG: startup process (PID 17452) was terminated by signal 9 <2005-03-28 13:52:00 EST >LOG: 0: background writer process (PID 21456) was terminated by signal 9 <2005-03-28 15:26:25 EST 21453>LOG: 0: background writer process (PID 24526) was terminated by signal 9 <2005-03-28 15:39:16 EST 21453>LOG: 0: background writer process (PID 655) was terminated by signal 9 <2005-03-28 16:05:02 EST 21453>LOG: 0: background writer process (PID 1579) was terminated by signal 9 <2005-03-28 16:05:09 EST 21453>LOG: 0: background writer process (PID 4141) was terminated by signal 9 <2005-03-28 16:15:06 EST 21453>LOG: 0: background writer process (PID 4159) was terminated by signal 9 <2005-03-28 16:15:20 EST 21453>LOG: 0: background writer process (PID 5822) was terminated by signal 9 > Any chance you have a "terminate with extreme prejudice" daemon or > cron job that kills processes meeting certain conditions? Nope. I've been happily running pg on this server for many years. I'd frankly suspect the computer before Postgres, however, but a better error message (e.g. pointing to a corrupt disk or something) would be helpful. The first one was a server process and not a background writer process, FWIW. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503281939 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCSKR2vJuQZxSWSsgRAiR+AKCPRgwvtXYpS9svHPEwxYhn+OXjqwCeIlZr +YywueTkKDXTdSFdIHasVhY= =MvWn -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] Background writer process terminating
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a server running 8.0.1 which keeps terminating. I first noticed this today: it has happened about 7 times already. Periodically, the background writer process is killed and drags the rest of the server down with it. Most times PG restarts on its own, but twice I've found a dead server on the floor and had to resusciate it manually with pg_ctl start. Here's a log snippet: <2005-03-28 12:28:27 EST >LOG: background writer process (PID 17409) was terminated by signal 9 <2005-03-28 12:28:27 EST >LOG: terminating any other active server processes <2005-03-28 12:28:27 EST >LOG: all server processes terminated; reinitializing <2005-03-28 12:28:27 EST >LOG: database system was interrupted at 2005-03-28 12:27:49 EST <2005-03-28 12:28:41 EST >LOG: startup process (PID 17452) was terminated by signal 9 <2005-03-28 12:28:41 EST >LOG: aborting startup due to startup process failure I bumped the logging way up and caught it happening again. The logs are too big to reproduce here, but can be found at: http://www.gtsm.com/pgboom3.txt Memory seems to be ok. No entries in /var/log/messages (this is a Linux 2.4 series kernel, gcc 3.4.1, dual 686 CPU, 1 SCSI drive system). There are two other 8.01 servers running on this box: they are also experiencing this problem, but only once each so far. There was a recent problem with running out of filehandles, but this was boosted and is probably not an issue now. Plenty of disk space on the partitions. Suggestions and hints very welcome. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503281839 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCSJazvJuQZxSWSsgRAk1MAJ0R8Rc4V5ud5XWkdNNZ9/logSdiIgCg05+I ufwoOhbUaHQIZQ14SerUYbY= =2XvT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] plpgsql For SQLQuery Loop Flags Error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I get error "missing ".." at end of SQL expression" ... > Can someone help please The problems appear to be in your declare block: > curTrackList char(15) ALIAS for $1; > sliceFile varchar ALIAS for $2; > lmfpLimit integer ALIAS for $3 You need a semicolon after the #3, and if declaring a variable as an alias, you do /not/ put in the data type (because you already declared it). In other words: curTrackList ALIAS fOR $1; sliceFileALIAS FOR $2; lmfpLimitALIAS FOR $3; That may or may not be what is causing the error you saw: plpgsql's error reporting is not always as helpful as it should be. Just start at the top of the file and work your way down, looking for easy stuff. Then cut things out of the function until it works, and add things back in a line at a time until you figure out the problem(s). - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200312182010 -BEGIN PGP SIGNATURE- iD8DBQE/4lDovJuQZxSWSsgRAulsAJ0bvUiXWiKmUDLV6esHoZtuQ6D5eQCgi5AA xKH9t+TM59YbXa2dc7CyjRY= =yapu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] Detecting proper bison version before make
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I just installed Postgres on an older machine and was surprised that configure did not throw an error about running an old version of bison, but let me get a bit into the whole 'make' cycle before a cryptic error was thrown. Can the configure script be made to check for a proper version of bison? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200306012154 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+2q7qvJuQZxSWSsgRAjmqAKDOWFXmckpYnvrIXSKVY7CQNWcn2gCbBb7b il6Rrr+MmQ4fUrFNN4dRNyM= =2Y3s -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
