Re: [HACKERS] pltcl broken on tcl8.5 ?
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Hoo, nasty. Tcl_GetVar() is resetting interp-result. According to the manual page that's only supposed to happen if the TCL_LEAVE_ERR_MSG flag is used: TCL_LEAVE_ERR_MSG If an error is returned and this bit is set in flags, then an error message will be left in the interpreter’s result, where it can be retrieved with Tcl_GetObjResult or Tcl_GetStringResult. If this flag bit isn’t set then no error message is left and the interpreter’s result will not be modified. But notice that they specify using Tcl_GetObjResult or Tcl_GetStringResult, rather than touching the field directly. I suspect they'd regard this as not-a-bug. In any case I found the responsible code: Tcl_SaveInterpState/Tcl_RestoreInterpState restore the result as an object not a string. I see no such routines in 8.4. As I look at this, I think it's got even more problems: it's assuming that interp-result is in the database encoding, which seems a pretty faulty assumption considering it came from Tcl. pltcl_elog converts to the database encoding before calling Tcl_SetResult, which makes that path all right as long as Tcl doesn't do anything with the string before exiting, but I don't particularly trust that assumption either. All in all this is really quite broken. Yes. At any rate, your fix has worked on my test bed. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
On Monday 16 June 2008 21:12:23 Andrew Dunstan wrote:
David Fetter wrote:
On Mon, Jun 16, 2008 at 06:00:33PM -0400, Andrew Dunstan wrote:
I, too, would be happy to do the legwork on this one. I believe
we'd want to have both per-db and per-role settings for
search_path. What's involved with creating that latter?
Proper support for module install / uninstall will be a far better
solution. Why would you wast your time on something that will be at
best half-baked?
Maybe I'm missing something big, but I don't quite see what
constitutes proper that doesn't involve the module's having at least
one schema to itself. Does this mean we'd be freezing modules in
their first-deployed form? It seems to me that DROP SCHEMA ...
CASCADE is just the right level of modularity combined with
flexibility post-installation.
ISTM that uninstall foomodule will be a whole lot nicer.
If we record all the objects that the module contains, then we would
just drop them.
The module could involve one schema, or several schemas, or none.
Maybe that's the something big.
I think individual schemas is nicer, since it has helped me getting around
these problems for years now, while module support is still vaporware.
However, I am looking forward to your patch. :-)
BTW, I am suspecting part of your support will be giving pg_dump -m and -M
flags to control dumping or ignoring of specific modules?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing overhead for repeat de-TOASTing
On Jun 16, 2008, at 3:35 PM, Tom Lane wrote: to a cache entry rather than a freshly palloc'd value. The cache lookup key is the toast table OID plus value OID. Now pg_detoast_datum() has no ... the result of decompressing an inline-compressed datum, because those have no unique ID that could be used for a lookup key. This puts a bit of a Wouldn't the tid fit this? or table oid + tid? -- Jeff Trout [EMAIL PROTECTED] http://www.stuarthamm.net/ http://www.dellsmartexitin.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
Robert Treat wrote: On Monday 16 June 2008 21:12:23 Andrew Dunstan wrote: David Fetter wrote: On Mon, Jun 16, 2008 at 06:00:33PM -0400, Andrew Dunstan wrote: I, too, would be happy to do the legwork on this one. I believe we'd want to have both per-db and per-role settings for search_path. What's involved with creating that latter? Proper support for module install / uninstall will be a far better solution. Why would you wast your time on something that will be at best half-baked? Maybe I'm missing something big, but I don't quite see what constitutes proper that doesn't involve the module's having at least one schema to itself. Does this mean we'd be freezing modules in their first-deployed form? It seems to me that DROP SCHEMA ... CASCADE is just the right level of modularity combined with flexibility post-installation. ISTM that uninstall foomodule will be a whole lot nicer. If we record all the objects that the module contains, then we would just drop them. The module could involve one schema, or several schemas, or none. Maybe that's the something big. I think individual schemas is nicer, since it has helped me getting around these problems for years now, while module support is still vaporware. However, I am looking forward to your patch. :-) Perhaps you have missed the WIP patch for module install/uninstall that has already been submitted (not by me, by Tom Dunstan). Tom Lane has already pointed out why the schema idea is bad. I agree with every word he wrote. BTW, I am suspecting part of your support will be giving pg_dump -m and -M flags to control dumping or ignoring of specific modules? s/your/his/. Possibly. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plan cache vs regclass constants
The example here http://archives.postgresql.org/pgsql-novice/2008-06/msg00025.php shows that we are failing to regenerate cached plans when a table referenced by a regclass constant is removed. This is pretty minor in the big scheme of things, but it's still annoying since there is code in there that's explicitly intended to make that work (see fix_scan_expr in setrefs.c). I looked into why not, and soon realized that the example case really is 'tablename'::regclass::oid (ie, a RelabelType node atop a Const) because the constant is an argument to oideq(). So constant folding reduces that to just a Const of type OID, and fix_scan_expr doesn't think it's interesting. I see two ways this might be fixed: 1. Don't constant-fold this particular case. Costs: very ugly special case in constant folding, and a tiny execution-time penalty. 2. Make fix_scan_expr treat Consts of type OID like regclass Consts. Costs: some bogus additions to the list of relation OIDs a plan depends on, which would occasionally result in unnecessary replans. Anyone have a preference, or see a third way? I'm kind of leaning to #1, but it sure is ugly. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing overhead for repeat de-TOASTing
Jeff [EMAIL PROTECTED] writes: On Jun 16, 2008, at 3:35 PM, Tom Lane wrote: the result of decompressing an inline-compressed datum, because those have no unique ID that could be used for a lookup key. This puts a bit of a Wouldn't the tid fit this? or table oid + tid? No. The killer reason why not is that at the time we need to decompress a datum, we don't know what row it came from. There are some other problems too... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] sh - pl
Folks, I've noticed that a big hunk of our build system has gratuitous dependencies on some variety of shell and on tools like sed, none of which makes Windows developers feel welcome. I know people are working toward a cmake or other more cross-platform toolchain. My proposal is a lot more modest, and doesn't conflict with the larger one. I'd like to move the above stuff to self-contained perl would help to make things more cross-platform and clean up, no offense to the fine authors, some pretty crufty code in there. Comments? Objections? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
David Fetter wrote: Folks, I've noticed that a big hunk of our build system has gratuitous dependencies on some variety of shell and on tools like sed, none of which makes Windows developers feel welcome. I know people are working toward a cmake or other more cross-platform toolchain. My proposal is a lot more modest, and doesn't conflict with the larger one. I'd like to move the above stuff to self-contained perl would help to make things more cross-platform and clean up, no offense to the fine authors, some pretty crufty code in there. Give us some examples. (If you think the sed scripts are crufty, check out some of the awk we use.) In the case of sed, there is a standard perl replacement called psed which can be used in at least simple cases. But nothing we do to the make-based build system will make Windows developers wanting to use MSVC feel any better, so I'm not quite sure exactly what you would achieve. Is perl currently required to build from tarball? If not, you would be placing an additional build requirement and there may still be a few odd build environments that don't sport perl by default. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
David Fetter [EMAIL PROTECTED] writes: My proposal is a lot more modest, and doesn't conflict with the larger one. I'd like to move the above stuff to self-contained perl would help to make things more cross-platform and clean up, no offense to the fine authors, some pretty crufty code in there. This seems quite useless. Make scripts always rely on a shell environment; you won't be buying any portability at all. We might or might not want to switch to cmake, but I don't see any value in half-measures. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
On Mon, Jun 16, 2008 at 09:43:37PM -0400, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: David Fetter wrote: Maybe I'm missing something big, but I don't quite see what constitutes proper that doesn't involve the module's having at least one schema to itself. ISTM that uninstall foomodule will be a whole lot nicer. Right. We have all the mechanism we need in the form of the dependency stuff: you just make everything in the module auto-depend on the module object. People who want to put their modules into private schemas can do it, but they won't be forced to. It's not quite that simple. Let's say you're *developing* a module. I don't see any way to play with it in the separate module proposal, where I *do* see a whole extra non-orthogonal feature where none is needed. No way to do optional submodules, either, and I'm sure there are plenty of other nasty limitations. Here's how what I'm proposing would work: 1. Create a way for schemas themselves to depend on other schemas, *not* on the stuff inside. This would make dependency an extremely simple problem, which is to say that DROP SCHEMA my_app CASCADE would cause anything depending on it, all the way down to the leaves in the DAG, to get dropped. Without CASCADE, it would Do The Right Thing™, i.e. throw an error. 2. Create a way to readjust search_paths per-db and per-role, as previously proposed. 3. Create wrappers like (UN)INSTALL MODULE using the two tools above. No, they would not necessarily appear in pg_dump. Tom, please don't paint us into a corner. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
David Fetter wrote: It's not quite that simple. Let's say you're *developing* a module. I don't see any way to play with it in the separate module proposal, where I *do* see a whole extra non-orthogonal feature where none is needed. No way to do optional submodules, either, and I'm sure there are plenty of other nasty limitations. Maybe what's needed here is just some more additional commands (i.e. add this function to the module, this module is dependent on this other module). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
On Tue, Jun 17, 2008 at 10:19:59AM -0400, Andrew Dunstan wrote: David Fetter wrote: Folks, I've noticed that a big hunk of our build system has gratuitous dependencies on some variety of shell and on tools like sed, none of which makes Windows developers feel welcome. I know people are working toward a cmake or other more cross-platform toolchain. My proposal is a lot more modest, and doesn't conflict with the larger one. I'd like to move the above stuff to self-contained perl would help to make things more cross-platform and clean up, no offense to the fine authors, some pretty crufty code in there. Give us some examples. That new version stamper calls out to sed, when perl is perfectly capable of doing the same work itself and not spawning 30 shells in the process. (If you think the sed scripts are crufty, check out some of the awk we use.) Another cleanup opportunity :) In the case of sed, there is a standard perl replacement called psed which can be used in at least simple cases. But nothing we do to the make-based build system will make Windows developers wanting to use MSVC feel any better, so I'm not quite sure exactly what you would achieve. Well, it'll wind up with a build system that's documented a lot better than it is :) Is perl currently required to build from tarball? If not, you would be placing an additional build requirement and there may still be a few odd build environments that don't sport perl by default. This is 2008, and it's silly to pretend we need to support this requirement on systems where people are building Postgres. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
Alvaro Herrera [EMAIL PROTECTED] writes: Maybe what's needed here is just some more additional commands (i.e. add this function to the module, this module is dependent on this other module). Yeah. Didn't we have this discussion already? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Maybe what's needed here is just some more additional commands (i.e. add this function to the module, this module is dependent on this other module). Yeah. Didn't we have this discussion already? I don't know -- I skipped it. Sorry. Blame it on Dave Fetter :-P -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
David Fetter wrote: On Tue, Jun 17, 2008 at 10:19:59AM -0400, Andrew Dunstan wrote: David Fetter wrote: Folks, I've noticed that a big hunk of our build system has gratuitous dependencies on some variety of shell and on tools like sed, none of which makes Windows developers feel welcome. I know people are working toward a cmake or other more cross-platform toolchain. My proposal is a lot more modest, and doesn't conflict with the larger one. I'd like to move the above stuff to self-contained perl would help to make things more cross-platform and clean up, no offense to the fine authors, some pretty crufty code in there. Give us some examples. That new version stamper calls out to sed, when perl is perfectly capable of doing the same work itself and not spawning 30 shells in the process. I thought you might be thinking of that. In fact, you are completely mischaracterising this script, which is not any part of the build system. It is a maintenance tool, probably of practical interest to about three people, and there is no requirement for it to be cross-platform. Is perl currently required to build from tarball? If not, you would be placing an additional build requirement and there may still be a few odd build environments that don't sport perl by default. This is 2008, and it's silly to pretend we need to support this requirement on systems where people are building Postgres. Weren't you the person who just wanted not to be painted into a corner? In general, I am in favor of having as few build dependencies as possible. So should you be. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
David Fetter wrote: That new version stamper calls out to sed, when perl is perfectly capable of doing the same work itself and not spawning 30 shells in the process. That's great. Please send a patch to improve the stamper. (Are you really worried about its performance, given that it runs about once a month on average?) (If you think the sed scripts are crufty, check out some of the awk we use.) Another cleanup opportunity :) I'm not sure it's the same case here, because the stamper is supposed to run on very few machines (mostly just Marc's) whereas the rest of the stuff is supposed to run on many others. Is perl currently required to build from tarball? If not, you would be placing an additional build requirement and there may still be a few odd build environments that don't sport perl by default. This is 2008, and it's silly to pretend we need to support this requirement on systems where people are building Postgres. Maybe, or maybe not. Do these platforms all have Perl? gypsy_moth Solaris 8 SUN Studio 8 sparc warthog UnixWare 7.1.4 cc 4.2 isa canary NetBSD 1.6 gcc 2.95.3 x86 kuduSolaris 9 Sun WorkShop 6 update 2 C 5.3 x86 spoonbill OpenBSD OpenBSD 4.2 gcc gcc 3.3.5 Sparc64 grebe AIX 5.3 GCC 4.0.1 PPC osprey NetBSD 2.0 gcc 3.3.3 m68k -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
David Fetter wrote: On Tue, Jun 17, 2008 at 10:19:59AM -0400, Andrew Dunstan wrote: David Fetter wrote: Folks, Well, it'll wind up with a build system that's documented a lot better than it is :) Is perl currently required to build from tarball? If not, you would be placing an additional build requirement and there may still be a few odd build environments that don't sport perl by default. This is 2008, and it's silly to pretend we need to support this requirement on systems where people are building Postgres. I am curious what your overall proposal includes? Would I do: perl Makefile.PL; make? Or would things like autoconf and bison still be required? Or are you just presenting to remove all the underlying nits that are tied to all the different unix derived utilities? Sincerely, Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
On Tue, Jun 17, 2008 at 11:00:31AM -0400, Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Maybe what's needed here is just some more additional commands (i.e. add this function to the module, this module is dependent on this other module). Yeah. Didn't we have this discussion already? I don't know -- I skipped it. Sorry. Blame it on Dave Fetter :-P Everything is my fault :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
David Fetter wrote: Yeah. Didn't we have this discussion already? I don't know -- I skipped it. Sorry. Blame it on Dave Fetter :-P Everything is my fault :) You finally understand! Joshua D. Drake Cheers, David. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Crash in pgCrypto?
David Fetter [EMAIL PROTECTED] writes: It's not quite that simple. Let's say you're *developing* a module. I don't see any way to play with it in the separate module proposal, where I *do* see a whole extra non-orthogonal feature where none is needed. The claim that no new feature is needed is complete rubbish. The *main* thing that we need to get out of a module concept is to have pg_dump know that it should not dump objects that are part of a module (at least in the default case). That can't be the behavior for schemas. You could imagine implementing modules as specially marked schemas, perhaps, but I don't see any particular advantage to that. In particular, I don't want to force people to play around with search_path in order to use modules. Here's how what I'm proposing would work: 1. Create a way for schemas themselves to depend on other schemas, *not* on the stuff inside. That does not actually solve any problem we need solved. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
Alvaro Herrera [EMAIL PROTECTED] writes: David Fetter wrote: This is 2008, and it's silly to pretend we need to support this requirement on systems where people are building Postgres. Maybe, or maybe not. Do these platforms all have Perl? In this connection it might be worth pointing to the Red Hat/Fedora definition of the standard minimum build environment: https://fedoraproject.org/wiki/Packaging/Guidelines#Exceptions which I can assure you is no spur-of-the-moment list but was very carefully chosen. It has awk, and sed ... but not perl. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
On Tue, 17 Jun 2008, Alvaro Herrera wrote: Maybe, or maybe not. Do these platforms all have Perl? Of course. They're all buildfarm clients and the buildfarm script is perl. Kris Jurka -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a
database which contains sequences in any of 8.1, 8.2, or 8.3:
[...]
--
-- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema:
mercado; Owner: prod
--
SELECT
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
'transaction_id'), 6736138, true);
--
-- Name: transaction; Type: TABLE; Schema: mercado; Owner: prod; Tablespace:
--
CREATE TABLE transaction (
transaction_id integer DEFAULT
nextval('transaction_transaction_id_seq'::regclass) NOT NULL,
buyer_account_id integer,
seller_account_id integer,
date date,
item_id integer,
source text
);
[...]
2008-06-16 19:26:41 PDT ERROR: relation transaction_transaction_id_seq
does not exist
Why? Because pg_dump mysteriously omits all sequences:
think=# \d transaction_transaction_id_seq
Sequence mercado.transaction_transaction_id_seq
Column | Type
---+-
sequence_name | name
last_value| bigint
increment_by | bigint
max_value | bigint
min_value | bigint
cache_value | bigint
log_cnt | bigint
is_cycled | boolean
is_called | boolean
think=# \ds
List of relations
Schema |Name| Type | Owner
-++--+---
mercado | account_account_id_seq | sequence | prod
mercado | account_stat_account_stat_id_seq | sequence | prod
mercado | category_category_id_seq | sequence | prod
mercado | category_stat_category_stat_id_seq | sequence | prod
mercado | country_country_id_seq | sequence | prod
mercado | country_stat_country_stat_id_seq | sequence | prod
mercado | dict_dict_id_seq | sequence | prod
mercado | expire_icon_expire_icon_id_seq | sequence | prod
mercado | expire_time_expire_time_id_seq | sequence | prod
mercado | fx_fx_id_seq | sequence | prod
mercado | icon_icon_id_seq | sequence | prod
mercado | item_icon_item_icon_id_seq | sequence | prod
mercado | item_item_id_seq | sequence | prod
mercado | item_stat_item_stat_id_seq | sequence | prod
mercado | transaction_transaction_id_seq | sequence | prod
(15 rows)
[EMAIL PROTECTED]:~$ pg_dump -s -n mercado think | grep CREATE\ SEQUENCE
[EMAIL PROTECTED]:~$
Therefore when the restore is attempted, the table using the sequence as
default value cannot be created.
[HACKERS] Cleaning up cross-type arithmetic operators
There was a discussion back here: http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php that came to the conclusion that cross-type operators are a bad idea if they don't come in complete sets: if you don't have an exact match to the input types, and there are multiple possible candidates, then the system doesn't know what to pick. I looked through pg_operator just now, and found that we seem to be okay as far as comparison operators go, but we do have issues for basic arithmetic operators. Specifically, these cross-type operators aren't part of complete sets: OID | OPERATOR 548 | % (smallint,integer) 549 | % (integer,smallint) 690 | * (bigint,integer) 544 | * (smallint,integer) 694 | * (integer,bigint) 545 | * (integer,smallint) 688 | + (bigint,integer) 552 | + (smallint,integer) 692 | + (integer,bigint) 553 | + (integer,smallint) 689 | - (bigint,integer) 556 | - (smallint,integer) 693 | - (integer,bigint) 557 | - (integer,smallint) 691 | / (bigint,integer) 546 | / (smallint,integer) 695 | / (integer,bigint) 547 | / (integer,smallint) We could either remove all of these, or fill in the sets. Removal would mean that cross-type cases would get implemented as a coercion function feeding a single-data-type operator, which would be marginally slower to execute (I'm not sure it would be significant). What I'm inclined to do is remove the two % operators, which don't seem likely to be performance-critical, and fill in the missing int2-vs-int8 cases for the four basic arithmetic operators. But I could be talked into just nuking everything listed above (and their underlying functions of course). Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Jeffrey Baker [EMAIL PROTECTED] writes: It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a database which contains sequences in any of 8.1, 8.2, or 8.3: I should think we would have heard about it before now if such a sweeping claim were true. What I suspect is that you are using 8.1's pg_dump, and you have tripped over one of the corner cases that made us redesign dumping of serial sequences for 8.2. Do you get better results if you dump the problem database with 8.2 or 8.3 pg_dump? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing overhead for repeat de-TOASTing
But we can resolve that by ruling that the required lifetime is the same as the value would have had if it'd really been palloc'd --- IOW, until the memory context that was current at the time gets deleted or reset. Many support functions of GiST/GIN live in very short memory context - only for one call. So, that cache invalidation technique doesn't give any advantage without rearranging this part. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing overhead for repeat de-TOASTing
Teodor Sigaev [EMAIL PROTECTED] writes: But we can resolve that by ruling that the required lifetime is the same as the value would have had if it'd really been palloc'd --- IOW, until the memory context that was current at the time gets deleted or reset. Many support functions of GiST/GIN live in very short memory context - only for one call. So, that cache invalidation technique doesn't give any advantage without rearranging this part. Right, but I think I've got that covered. The memory context reset won't actually flush the toast cache entry, it effectively just drops its reference count. We'll only drop cache entries when under memory pressure (or if they're invalidated by toast table updates/deletes). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cleaning up cross-type arithmetic operators
On Tue, Jun 17, 2008 at 01:29:56PM -0400, Tom Lane wrote: ... What I'm inclined to do is remove the two % operators, which don't seem likely to be performance-critical, and fill in the missing int2-vs-int8 cases for the four basic arithmetic operators. But I could be talked into just nuking everything listed above (and their underlying functions of course). Comments? regards, tom lane +1 for getting rid of the % operators and fleshing out the cases for the four basic arithmetic operators. Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane [EMAIL PROTECTED] wrote: Jeffrey Baker [EMAIL PROTECTED] writes: It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a database which contains sequences in any of 8.1, 8.2, or 8.3: I should think we would have heard about it before now if such a sweeping claim were true. Last time this problem came up, in August, you dismissed it somewhat rudely. So perhaps the lack of reports is due more to perception than any other thing. What I suspect is that you are using 8.1's pg_dump, and you have tripped over one of the corner cases that made us redesign dumping of serial sequences for 8.2. Do you get better results if you dump the problem database with 8.2 or 8.3 pg_dump? What's the corner case exactly? 8.3 dumps it correctly, but that's not really much of a consolation because I need to restore _this_ dump, not some other one. It was necessary for me to recreate all the sequences and set the curvals manually. Can't the fix be backported to 8.1? -jwb
Re: [HACKERS] Reducing overhead for repeat de-TOASTing
I definitely think it's worth it, even if it doesn't handle an inline-compressed datum. Yeah. I'm not certain how much benefit we could get there anyway. If the datum isn't out-of-line then there's a small upper limit on how big it can be and hence a small upper limit on how long it takes to decompress. It's not clear that a complicated caching scheme would pay for itself. Well there's a small upper limit per-instance but the aggregate could still be significant if you have a situation like btree scans which are repeatedly detoasting the same datum. Note that the inline compressed case includes packed varlenas which are being copied just to get their alignment right. It would be nice to get rid of that palloc/pfree bandwidth. I don't really see a way to do this though. If we hook into the original datum's mcxt we could use the pointer itself as a key. But if the original datum comes from a buffer that doesn't work. One thought I had -- which doesn't seem to go anywhere, but I thought was worth mentioning in case you see a way to leverage it that I don't -- is that if the toast key is already in the cache then deform_tuple could substitute the cached value directly instead of waiting for someone to detoast it. That means we can save all the subsequent trips to the toast cache manager. I'm not sure that would give us a convenient way to know when to unpin the toast cache entry though. It's possible that some code is aware that deform_tuple doesn't allocate anything currently and therefore doesn't set the memory context to anything that will live as long as the data it returns. Incidentally, I'm on vacation and reading this via an awful webmail interface. So I'm likely to miss some interesting stuff for a couple weeks. I suppose the Snr ratio of the list is likely to move but I'm not sure which direction...
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Jeffrey Baker escribió: On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane [EMAIL PROTECTED] wrote: Jeffrey Baker [EMAIL PROTECTED] writes: It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a database which contains sequences in any of 8.1, 8.2, or 8.3: I should think we would have heard about it before now if such a sweeping claim were true. Last time this problem came up, in August, you dismissed it somewhat rudely. So perhaps the lack of reports is due more to perception than any other thing. How did you set it up exactly? I have no problem with this situation: $ psql Welcome to psql 8.1.10, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit alvherre=# create table foo (a serial); NOTICE: CREATE TABLE will create implicit sequence foo_a_seq for serial colum n foo.a CREATE TABLE alvherre=# \q $ pg_dump -t foo | psql foo SET SET SET SET SET SET CREATE TABLE ALTER TABLE setval 1 (1 row) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
On Tue, Jun 17, 2008 at 2:43 PM, Alvaro Herrera [EMAIL PROTECTED]
wrote:
Jeffrey Baker escribió:
On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane [EMAIL PROTECTED] wrote:
Jeffrey Baker [EMAIL PROTECTED] writes:
It is impossible to dump (with pg_dump -Ocx) and restore (with psql)
a
database which contains sequences in any of 8.1, 8.2, or 8.3:
I should think we would have heard about it before now if such a
sweeping claim were true.
Last time this problem came up, in August, you dismissed it somewhat
rudely. So perhaps the lack of reports is due more to perception than
any
other thing.
How did you set it up exactly? I have no problem with this situation:
[snip]
The table was originally created this way:
CREATE TABLE transaction
(
transaction_id SERIAL PRIMARY KEY,
buyer_account_idINTEGER,
seller_account_id INTEGER,
dateDATE,
item_id INTEGER,
source TEXT
);
However, when dumped with pg_dump 8.1, it comes out this way:
CREATE TABLE transaction (
transaction_id integer DEFAULT
nextval('transaction_transaction_id_seq'::regclass) NOT NULL,
buyer_account_id integer,
seller_account_id integer,
date date,
item_id integer,
source text
);
.. and the sequence does not get dumped with it.
-jwb
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Jeffrey Baker escribió: The table was originally created this way: CREATE TABLE transaction ( transaction_id SERIAL PRIMARY KEY, buyer_account_idINTEGER, seller_account_id INTEGER, dateDATE, item_id INTEGER, source TEXT ); Okay, but was it created on 8.1 or was it already created on an older version and restored? I don't see this behavior if I create it in 8.1 -- the field is dumped as SERIAL, unlike what you show. -- -- Name: transaction; Type: TABLE; Schema: public; Owner: alvherre; Tablespace: -- CREATE TABLE transaction ( transaction_id serial NOT NULL, buyer_account_id integer, seller_account_id integer, date date, item_id integer, source text ); $ pg_dump --version pg_dump (PostgreSQL) 8.1.10 -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
Alvaro Herrera wrote: Maybe, or maybe not. Do these platforms all have Perl? gypsy_moth Solaris 8 SUN Studio 8 spar If the moths don't have perl, we'll add it, no problem - don't let that stop anything. (On a separate note, we have had some problems internally with DNS, so some reporting has failed from some of out moths, it is a Sun internal infrastructure problem which we are looking into.) -Jørgen -- Sun Database Group http://blogs.sun.com/austvik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sh - pl
Jorgen Austvik wrote: Alvaro Herrera wrote: Maybe, or maybe not. Do these platforms all have Perl? gypsy_moth Solaris 8 SUN Studio 8 spar If the moths don't have perl, we'll add it, no problem - don't let that stop anything. Of course they have perl - the buildfarm script is perl. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] regex cache
Folks, I'm doing some analysis of PostgreSQL site traffic, and am being frequently hung up by the compile-time-fixed size of our regex cache (32 regexes, per MAX_CACHED_RES). Is there a reason why it would be hard to use work_mem or some other dynamically changeable limit for regex caching? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Alvaro Herrera [EMAIL PROTECTED] writes:
Jeffrey Baker escribió:
The table was originally created this way:
Okay, but was it created on 8.1 or was it already created on an older
version and restored? I don't see this behavior if I create it in 8.1
-- the field is dumped as SERIAL, unlike what you show.
There's something interesting in the original report:
--
-- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema: mercado;
Owner: prod
--
SELECT
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
^^
'transaction_id'), 6736138, true);
So pg_dump found a pg_depend entry linking that sequence to some table
named transaction_backup, not transaction. That explains why
transaction isn't being dumped using a SERIAL keyword --- it's not
linked to this sequence. But how things got this way is not apparent
from the stated facts.
One possibility is that Jeffrey is getting bit by this bug or
something related:
http://archives.postgresql.org/pgsql-bugs/2006-07/msg00021.php
There are links to some other known serial-sequence problems in 8.1
in this message:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01250.php
None of those reports seem to exactly match the described behavior, but
anyway I'd bet a good deal that either the table or the sequence has
been altered in some way since they were created. Given that Jeffrey
says all his sequences fail the same way, it must've been something
he did to all his tables/sequences ...
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
On Tue, Jun 17, 2008 at 6:31 PM, Tom Lane [EMAIL PROTECTED] wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Jeffrey Baker escribió:
The table was originally created this way:
Okay, but was it created on 8.1 or was it already created on an older
version and restored? I don't see this behavior if I create it in 8.1
-- the field is dumped as SERIAL, unlike what you show.
There's something interesting in the original report:
--
-- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema:
mercado; Owner: prod
--
SELECT
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
^^
'transaction_id'), 6736138, true);
So pg_dump found a pg_depend entry linking that sequence to some table
named transaction_backup, not transaction. That explains why
transaction isn't being dumped using a SERIAL keyword --- it's not
linked to this sequence. But how things got this way is not apparent
from the stated facts.
Hrmm, I think that's a bit of a red herring. I probably should not have
pasted that part of the dump, because it's misleading. There really is a
table transaction_backup, definition is the same as transaction.
Reading from that part of the dump again, just for clarity:
--
-- Name: transaction_backup; Type: TABLE; Schema: mercado; Owner: prod;
Tablespace:
--
CREATE TABLE transaction_backup (
transaction_id serial NOT NULL,
buyer_account_id integer,
seller_account_id integer,
date date,
item_id integer,
source text
);
ALTER TABLE mercado.transaction_backup OWNER TO prod;
--
-- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema:
mercado; Owner: prod
--
SELECT
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
'transaction_id'), 6736139, true);
--
-- Name: transaction; Type: TABLE; Schema: mercado; Owner: prod; Tablespace:
--
CREATE TABLE transaction (
transaction_id integer DEFAULT
nextval('transaction_transaction_id_seq'::regclass) NOT NULL,
buyer_account_id integer,
seller_account_id integer,
date date,
item_id integer,
source text
);
ALTER TABLE mercado.transaction OWNER TO prod;
The two tables are defined the same way, but one of them gets dumped with a
SERIAL declaration and the other gets dumped with a DEFAULT nextval().
Is it possible that pg_dump became confused if transaction was renamed
transaction_backup and then redefined? I can't guarantee that did in fact
happen, but it's within the realm of possibility. I don't see the backup
table in the sql source code for this product, so it's likely that it was
created by a user in the course of maintenance.
One possibility is that Jeffrey is getting bit by this bug or
something related:
http://archives.postgresql.org/pgsql-bugs/2006-07/msg00021.php
I don't think it's that one. All this stuff is in the same schema (and in
any case the dump file contains all schemas).
There are links to some other known serial-sequence problems in 8.1
in this message:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01250.php
That one seems closer to the point.
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01250.php
-jwb
Re: [HACKERS] Crash in pgCrypto?
Coming to this thread a bit late as I've been out of email connectivity for the past week... On Tue, Jun 17, 2008 at 2:43 AM, Tom Lane [EMAIL PROTECTED] wrote: In any case, trying to define a module as a schema doesn't help at all to solve the hard problem, which is how to get this stuff to play nice with pg_dump. I think that the agreed-on solution was that pg_dump should emit some kind of LOAD MODULE foo command, and *not* dump any of the individual objects in the module. We can't have that if we try to equate modules with schemas instead of making them a new kind of object. This is certainly the end result that I'd like, and intend to work towards. My main concern has been cases where a module-owned table gets updated with data that would not be recreated/updated by the LOAD MODULE in the dump. PostGIS support tables are one example of this, PL/Java classpath / function information is another. There are probably many more. I see two potential solutions: a) explicitly mark such tables as requiring data to be dumped somehow, and have pg_dump emit upsert statements for all rows in the table. b) allow modules to define a function that can pg_dump can call to emit appropriate extra restore commands, above whatever LOAD MODULE foo does. This has the downside of requiring more work from module owners (though perhaps a default function that effectively does option a) could be provided), with a potential upside of allowing module dumps to become upgrade-friendly by not being tied to a particular version's table layout. Thoughts? Tom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regex cache
Josh Berkus [EMAIL PROTECTED] writes: I'm doing some analysis of PostgreSQL site traffic, and am being frequently hung up by the compile-time-fixed size of our regex cache (32 regexes, per MAX_CACHED_RES). Is there a reason why it would be hard to use work_mem or some other dynamically changeable limit for regex caching? Hmmm ... Spencer's regex library makes a point of hiding its internal representation of a compiled regex from the calling code. So measuring the size of the regex cache in bytes would involve doing a lot of violence to that API. We could certainly allow the size of the cache measured in number-of-regexes to be controlled, though. Having said that, I'm not sure it'd help your problem. If your query is using more than 32 regexes concurrently, it likely is using $BIGNUM regexes concurrently. How do we fix that? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
