Re: [HACKERS] [PATCHES] pg_regress in C
On Tue, Jul 18, 2006 at 10:46:04PM -0400, Tom Lane wrote: ... One reason I didn't try to do this is I'm a bit hesitant to write a signal handler that does anything as interesting as a system() call, which would seem to be necessary to duplicate what the shell script did. Comments? It might not actually be unsafe, given system() actually blocks on waitpid() which is specifically listed as a safe function. I'm a bit confused though, because system() generally sets the parent to ignore SIGINT which running the child process. That means the postmaster is being killed but pg_regress is not? If this is the case, then you won't be able to catch SIGINT anyway. Also, the kernel sending it to everyone on the same terminal is (AIUI) only true if you're running under the same session ID. postgres only daemonizes itself to be immune from frontend terminal interrupts (using setsid) when silent_mode is on. I think it defaults to off, which is probably why it works at all. Anyway, the signal handling for Windows involves a seperate thead AIUI which may make it easier. It might be interesting to see how bash does it. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Possible Typecasting Bug with coalesce()
= select now()coalesce('Jul 14 2006 9:16:47AM');
The only bug I see here is that implicit coercions to text
are a bad idea :-( --- IMHO it would be better if your first
query failed instead of giving you unexpected behavior.
:-) We know that you think that Tom, but a lot of us do not
want to go casting all our sql, especially where other db's don't
require it.
Would an equivalent CASE statement also do the early conversion to text
?
Andreas
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
I've seen a few EAV designs in practice. They've all been problematic. I'd like to have a better way of dealing with them. Which is why I'm tentatively suggesting support for inheritance and constraints in views. If there's some other way to achieve constraint based exclusion across a UNION of heterogenous tables, I'd love to hear it. If you do your own rules anyway, why can't you use inheritance and create the rules on the parent table and the constraints on the child tables ? You can still use the child tables directly if you want. Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
I've seen a few EAV designs in practice. They've all been problematic. I'd like to have a better way of dealing with them. Which is why I'm tentatively suggesting support for inheritance and constraints in views. If there's some other way to achieve constraint based exclusion across a UNION of heterogenous tables, I'd love to hear it. If you do your own rules anyway, why can't you use inheritance and create the rules on the parent table and the constraints on the child tables ? Ah, sorry, just saw that you want different column names in your subtables. Add me as another vote to extend the new constraint elimination to union all views :-) Andreas ---(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: [HACKERS] RESET CONNECTION?
Will this patch make it into 8.2? http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php It's a really nice feature, would be extremly useful with tools like pgpool. No, it will not because RESET CONNECTION can mess up interface code that doesn't want the connection reset. We are not sure how to handle that. Imho, if it where at the protocol level, that would not be such an issue. If the interface gives access to the protocol level it is already depending on good will. Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pgxs problem
I've tracked down my problem with pgxs to Makefile.global in lib/pgxs/src. These lines seem to be the culprits: bindir := $(shell pg_config --bindir) datadir := $(shell pg_config --sharedir) sysconfdir := $(shell pg_config --sysconfdir) libdir := $(shell pg_config --libdir) pkglibdir := $(shell pg_config --pkglibdir) includedir := $(shell pg_config --includedir) pkgincludedir := $(shell pg_config --pkgincludedir) mandir := $(shell pg_config --mandir) docdir := $(shell pg_config --docdir) localedir := $(shell pg_config --localedir) I think it should be running $(pkglibdir)/bin/pg_config -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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
Re: [HACKERS] Progress bar updates
Tom Lane [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: I'm not quite sure what you're suggesting; presumably you'd need to open another client connection to send the status report message to a backend (since a backend will not be polling its input socket during query execution). That just seems like the wrong approach -- stashing a backend's current status into shared memory sounds more promising, IMHO, and won't require changes to the FE/BE protocol. Yeah, I was about to make the same comment. The new support for query status in shared memory should make it pretty cheap to update a progress indicator there, and then it'd be trivial to expose the indicator to other backends via pg_stat_activity. I think that would be a fine feature too. But I don't think that reduces the desire clients have to be able to request updates on the status of their own queries. In practice, if a query is taking long enough for this feature to be interesting, making another connection and looking to see what's happening is not a problem, and it's likely to be the most practical way anyway for many clients. It would be the most practical way for a DBA to monitor an application. But it's not going to be convenient for clients like pgadmin or psql. Even a web server may want to, for example, stream ajax code updating a progress bar until it has results and then stream the ajax to display the results. Having to get the backend pid before your query and then open a second database connection to monitor your first connection would be extra footwork for nothing. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Progress bar updates
Ühel kenal päeval, K, 2006-07-19 kell 05:18, kirjutas Greg Stark: Tom Lane [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: I'm not quite sure what you're suggesting; presumably you'd need to open another client connection to send the status report message to a backend (since a backend will not be polling its input socket during query execution). That just seems like the wrong approach -- stashing a backend's current status into shared memory sounds more promising, IMHO, and won't require changes to the FE/BE protocol. Yeah, I was about to make the same comment. The new support for query status in shared memory should make it pretty cheap to update a progress indicator there, and then it'd be trivial to expose the indicator to other backends via pg_stat_activity. I think that would be a fine feature too. But I don't think that reduces the desire clients have to be able to request updates on the status of their own queries. another \x command could be added to psql to do just that In practice, if a query is taking long enough for this feature to be interesting, making another connection and looking to see what's happening is not a problem, and it's likely to be the most practical way anyway for many clients. It would be the most practical way for a DBA to monitor an application. But it's not going to be convenient for clients like pgadmin or psql. Even a web server may want to, for example, stream ajax code updating a progress bar until it has results and then stream the ajax to display the results. Having to get the backend pid before your query and then open a second database connection to monitor your first connection would be extra footwork for nothing. You would have to do some extra work anyway. opening another connection is not such a big deal. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Progress bar updates
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Greg Stark Sent: 19 July 2006 10:19 To: Tom Lane Cc: Neil Conway; Gregory Stark; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Progress bar updates It would be the most practical way for a DBA to monitor an application. But it's not going to be convenient for clients like pgadmin or psql. Even a web server may want to, for example, stream ajax code updating a progress bar until it has results and then stream the ajax to display the results. Having to get the backend pid before your query and then open a second database connection to monitor your first connection would be extra footwork for nothing. No to mention that we already get occasional complaints about the number of connections pgAdmin can open (even though it's only one per database for the main app, plus one per query tool or data editor window). Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pgxs problem
On Wed, Jul 19, 2006 at 05:06:48AM -0400, Gregory Stark wrote: I've tracked down my problem with pgxs to Makefile.global in lib/pgxs/src. These lines seem to be the culprits: snip I think it should be running $(pkglibdir)/bin/pg_config Seems reasonable to me. This code definitly seems to be limiting you to one installation per machine. Setting the PATH would probably work too. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: CSStorm occurred again by postgreSQL8.2. (Re: [HACKERS] poor performance with Context Switch Storm at TPC-W.)
Tom Lane [EMAIL PROTECTED] wrote:
Katsuhiko Okano [EMAIL PROTECTED] writes:
It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
The problem was only postponed.
Can you provide a reproducible test case for this?
Seven machines are required in order to perform measurement.
(DB*1,AP*2,CLient*4)
Enough work load was not able to be given in two machines.
(DB*1,{AP+CL}*1)
It was not able to reappear to a multiplex run of pgbench
or a simple SELECT query.
TPC-W of a work load tool used this time is a full scratch.
Regrettably it cannot open to the public.
If there is a work load tool of a free license, I would like to try.
I will show if there is information required for others.
The patch which outputs the number of times of LWLock was used this time.
The following is old example output. FYI.
# SELECT * FROM pg_stat_lwlocks;
kind | pg_stat_get_lwlock_name | sh_call | sh_wait | ex_call |
ex_wait | sleep
--+++---+---+---+---
0 | BufMappingLock | 559375542 | 33542 |320092 |
24025 | 0
1 | BufFreelistLock| 0 | 0 |370709 |
47 | 0
2 | LockMgrLock| 0 | 0 | 41718885 |
734502 | 0
3 | OidGenLock | 33 | 0 | 0 |
0 | 0
4 | XidGenLock | 12572279 | 10095 | 11299469 |
20089 | 0
5 | ProcArrayLock |8371330 | 72052 | 16965667 |
603294 | 0
6 | SInvalLock | 38822428 | 435 | 25917 |
128 | 0
7 | FreeSpaceLock | 0 | 0 | 16787 |
4 | 0
8 | WALInsertLock | 0 | 0 | 1239911 |
885 | 0
9 | WALWriteLock | 0 | 0 | 69907 |
5589 | 0
10 | ControlFileLock| 0 | 0 | 16686 |
1 | 0
11 | CheckpointLock | 0 | 0 |34 |
0 | 0
12 | CheckpointStartLock| 69509 | 0 |34 |
1 | 0
13 | CLogControlLock| 0 | 0 |236763 |
183 | 0
14 | SubtransControlLock| 0 | 0 | 753773945 |
205273395 | 0
15 | MultiXactGenLock | 66 | 0 | 0 |
0 | 0
16 | MultiXactOffsetControlLock | 0 | 0 |35 |
0 | 0
17 | MultiXactMemberControlLock | 0 | 0 |34 |
0 | 0
18 | RelCacheInitLock | 0 | 0 | 0 |
0 | 0
19 | BgWriterCommLock | 0 | 0 | 61457 |
1 | 0
20 | TwoPhaseStateLock | 33 | 0 | 0 |
0 | 0
21 | TablespaceCreateLock | 0 | 0 | 0 |
0 | 0
22 | BufferIO | 0 | 0 |695627 |
16 | 0
23 | BufferContent | 3568231805 | 1897 | 1361394 |
829 | 0
24 | CLog | 0 | 0 | 0 |
0 | 0
25 | SubTrans | 138571621 | 143208883 | 8122181 |
8132646 | 0
26 | MultiXactOffset| 0 | 0 | 0 |
0 | 0
27 | MultiXactMember| 0 | 0 | 0 |
0 | 0
(28 rows)
I am pleased if interested.
regards,
Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp
---(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: CSStorm occurred again by postgreSQL8.2. (Re: [HACKERS] poor
Katsuhiko Okano wrote:
Tom Lane [EMAIL PROTECTED] wrote:
Katsuhiko Okano [EMAIL PROTECTED] writes:
It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
The problem was only postponed.
Can you provide a reproducible test case for this?
Seven machines are required in order to perform measurement.
(DB*1,AP*2,CLient*4)
Enough work load was not able to be given in two machines.
(DB*1,{AP+CL}*1)
It was not able to reappear to a multiplex run of pgbench
or a simple SELECT query.
TPC-W of a work load tool used this time is a full scratch.
Regrettably it cannot open to the public.
If there is a work load tool of a free license, I would like to try.
FYI: there is a free tpc-w implementation done by Jan available at:
http://pgfoundry.org/projects/tpc-w-php/
Stefan
---(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
[HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well
The psql manual says this: AUTOCOMMIT ... The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM). Unfortunately that's not precisely true. In fact psql cannot know whether the command can be executed within a transaction block since some commands, like CLUSTER and now CREATE INDEX, sometimes can and sometimes can't. The basic problem is that really we want to be able to run these commands even if a transaction has been started as long as nothing else has been done in that transaction (including the savepoint that psql also does automatically). Would it work to just check whether the serializable snapshot has been set? That would be simpler than the current logic. One possible criticism is that a user that manually does BEGIN; CLUSTER DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable. It seems to me that perhaps we want to somehow distinguish between manually invoked BEGIN where we would want to notify the user if they're trying to run something that will be committed automatically and implicit BEGIN which starts a new transaction but only the next time a transactional command is run. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: CSStorm occurred again by postgreSQL8.2. (Re: [HACKERS] poor
Hi folks,
From: Stefan Kaltenbrunner [EMAIL PROTECTED]
Subject: Re: CSStorm occurred again by postgreSQL8.2. (Re: [HACKERS] poor
Date: Wed, 19 Jul 2006 12:53:53 +0200
Katsuhiko Okano wrote:
Tom Lane [EMAIL PROTECTED] wrote:
Katsuhiko Okano [EMAIL PROTECTED] writes:
It does not solve, even if it increases the number of
NUM_SUBTRANS_BUFFERS.
The problem was only postponed.
Can you provide a reproducible test case for this?
Seven machines are required in order to perform measurement.
(DB*1,AP*2,CLient*4)
Enough work load was not able to be given in two machines.
(DB*1,{AP+CL}*1)
It was not able to reappear to a multiplex run of pgbench
or a simple SELECT query.
TPC-W of a work load tool used this time is a full scratch.
Regrettably it cannot open to the public.
If there is a work load tool of a free license, I would like to try.
FYI: there is a free tpc-w implementation done by Jan available at:
http://pgfoundry.org/projects/tpc-w-php/
FYI(2):
There is one more (pseudo) TPC-W implementation by OSDL.
http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/
One more comment is that Katsuhiko't team is using their own version of
TPC-W like benchmark suite, and he cannot make it public.
Also, his point is that he tried to reproduce the CSS phenomena using
pgbench and a proguram issuing heavily multiple SELECT queries
on a single table but they didn't work well reproducing CSS.
Regards,
Masanori
Stefan
---
Masanori ITOH NTT OSS Center, Nippon Telegraph and Telephone Corporation
e-mail: [EMAIL PROTECTED]
phone : +81-3-5860-5015
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] pg_regress breaks on msys
pg_regress now seems to break on Msys virtual locations: Example from the buildfarm: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=snakedt=2006-07-19%2009:00:00 == pgsql.4660/src/test/regress/log/initdb.log === The filename, directory name, or volume label syntax is incorrect. Surely this was tested when the original was prepared? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Progress bar updates
Josh Berkus wrote: Andreas, Some weeks ago I proposed a PROGRESS parameter for COPY, to enable progress feedback via notices. tgl thinks nobody needs that... Well, *Tom* doesn't need it. What mechanism did you propose to make this work? Extended the parser to accept that keyword, and emit notices when n lines were copied. I found that convenient when transferring a large amount of data, to estimate total runtime. Patch was submitted a while ago to -hackers, together with compression that was torn down in a way not suitable to inspire me to continue. Regards, Andreas Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] set search_path in dump output considered harmful
On Thu, Jul 13, 2006 at 07:17:31PM -0400, Tom Lane wrote: Phil Frost [EMAIL PROTECTED] writes: I've recently migrated one of my databases to using veil. This involved creating a 'private' schema and moving all tables to it. ... In doing so, I found to my extreme displeasure that although the database continues to function flawlessly, I can no longer restore dumps produced by pg_dump even after hours of manual tweaking. In all cases, this is due to search_path being frobbed during the restore. No, not one of these things can be blamed on pg_dump. CASE 1: serial column not in the same schema as associated table This is not supported. This fact is also not documented, and allowed without warning. Things that people can do to get the database into an unsupported state are most usually called bugs. CASE 2: default set to the serial sequence of another table This is actually the same thing as #1, because you did alter sequence private.t_i_seq set schema public; Agreed. CASE 3: functions containing unqualified function references This is a buggy function definition, plain and simple. It will fail just as much in the original database as in the dumped/restored one; just invoke it with a search_path other than what it's expecting. There has been talk of attaching a search_path setting to each function so that it's independent of the caller's search_path, but the performance hit seems a bit daunting. In any case it's not pg_dump's fault that this feature doesn't exist... I don't see anything in the documentation that says I must schema qualify every name reference in functions. Yes, I could break my function by changing the search_path but I bet this is true of most functions out there. Do you schema qualifiy all your operator references? Does this mean most functions are buggy? If this is the case, perhaps a warning should be issued! The fact is that search_path is an important setting that should not be frobbed lightly. You wouldn't arbitrarily change the header file's you have included in a C program, or clobber your OS's dynamic linker search path, would you? If pg_dump would only output qualified names always instead of setting search_path arbitrarily, this would not be a problem. I could run an alter database prior to restoring the dump as I have done on my production database and the dump could restore normally. Given that pg_dump already does this for many of the statements it outputs, I don't think it would be impossible to do it for all of them. I do not see any good arguments for the current behaviour. One was made that it allows one to change the schema of a collection of objects by editing a single line, but in fact this isn't true because half of the output statements contain a schema qualification dispite that search_path has been set. If there are any others that I am missing, please tell me. Other people have complained that the lack of schema qualified names makes the dump harder to read. Yesterday on IRC another user was looking for help because his backup wouldn't restore, and it seems the problem is related to search_path. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] password is no required, authentication is overridden
Andrew Dunstan wrote: It strikes me that this is actually a bad thing for pgadmin3 to be doing. It should use its own file, not the deafult location, at least if the libpq version is = 8.1. We provided the PGPASSFILE environment setting just so programs like this could use alternative locations for the pgpass file. Otherwise, it seems to me we are violating the POLS, as in the case of this user who not unnaturally thought he had found a major security hole. .pgpass is THE mechanism for storing libpq passwords, so what is wrong? If the account is assumed insecure, the user shouldn't check store password in pgadmin3. That's a libpq issue, not a pgadmin3 issue. Regards, Andreas ---(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: [HACKERS] password is no required, authentication is overridden
Hiroshi Saito wrote: From: Andrew Dunstan Thomas Bley wrote: + The .pgpass file will be automatically created if you're using pgAdmin III with store password being enabled in the connection settings. It strikes me that this is actually a bad thing for pgadmin3 to be doing. It should use its own file, not the deafult location, at least if the libpq version is = 8.1. We provided the PGPASSFILE environment setting just so programs like this could use alternative locations for the pgpass file. Otherwise, it seems to me we are violating the POLS, as in the case of this user who not unnaturally thought he had found a major security hole. Ummm, The function which pgAdmin offers is the optimal in present. I do not think that PGPASSFILE avoids the danger clearly. Probably, It is easy for the user who is malicious in the change to find it. I don't understand what you are saying here. The problem is that it is not clear (at least to the original user, and maybe to others) that when pgadmin3 saves a password it saves it where it will be found by all libpq clients, not just by pgadmin3. How is that optimal? If pgadmin3 were to save it in a non-standard location and then set PGPASSFILE to point to that location that would solve the problem. Or maybe it should offer a choice. Either way, how would a malicious user affect that? PGPASSFILE only contains a location, not the contents of the file, so exposing it is not any great security issue, as long as the location is itself protected. I consider it to be a problem that the password is finally PlainText. Then, I made the proposal before. However, It was indicated that deliberation is required again. I want to consider a good method again. Is there any proposal with good someone? Use of plaintext in pgpass files is a different problem. If you really want high security you need to get out of the game of shared passwords altogether, and use client certificates, IMNSHO. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] password is no required, authentication is overridden
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Dunstan Sent: 19 July 2006 13:55 To: Hiroshi Saito Cc: Thomas Bley; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] password is no required, authentication is overridden I don't understand what you are saying here. The problem is that it is not clear (at least to the original user, and maybe to others) that when pgadmin3 saves a password it saves it where it will be found by all libpq clients, not just by pgadmin3. From: http://www.pgadmin.org/docs/1.4/connect.html If you select store password, pgAdmin stores passwords you enter in the ~/.pgpass file under *nix or %APPDATA%\postgresql\pgpass.conf under Win32 for later reuse. For details, see pgpass documentation. It will be used for all libpq based tools. If you want the password removed, you can select the server's properties and uncheck the selection any time. How is that optimal? If pgadmin3 were to save it in a non-standard location and then set PGPASSFILE to point to that location that would solve the problem. Or maybe it should offer a choice. Either way, how would a malicious user affect that? PGPASSFILE only contains a location, not the contents of the file, so exposing it is not any great security issue, as long as the location is itself protected. We have no sensible way of determining whether or not the libpq we are running with supports PGPASSFILE. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] feature request: pg_dump --view
* Andrew Dunstan ([EMAIL PROTECTED]) wrote: elein wrote: pg_dump -t view_name will work. Oh, you got me all worked up. I was reading this as a way to dump the CONTENTS of a view not the DEFINITION of a view. I thought someone sneaked in pg_dump of a query in there. How would you load such a dump, since views are by default select-only? Create the appropriate table definition, and then load it up? Of course, an option to have the pg_dump include the table definition derived from the view would be nice also. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
On Wed, Jul 12, 2006 at 06:09:31PM -0400, Bruce Momjian wrote: Phil Frost wrote: On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote: Updated text: For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to quotelook up/ objects within the schema. Without this permission, it is still possible to see the object names by querying the system tables, but they cannot be accessed via SQL. No, this still misses the point entirely. See all my examples in this thread for ways I have accessed objects without usage to their schema with SQL. OK, well we are not putting a huge paragraph in there. Please suggest updated text. Well, if you won't explain the whole situation, nor change it, then all you can really say is it doesn't really work always. How about this: For schemas, allows access to objects contained in the specified schema. Note that the converse is not true in many cases: revoking usage on a schema is not sufficient to prevent access in all cases. There is precedent for new ways to bypass this check being added in future releases. It would be unwise to give this privilege much security value. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] pg_regress in C
Martijn van Oosterhout kleptog@svana.org writes: On Tue, Jul 18, 2006 at 10:46:04PM -0400, Tom Lane wrote: ... One reason I didn't try to do this is I'm a bit hesitant to write a signal handler that does anything as interesting as a system() call, which would seem to be necessary to duplicate what the shell script did. Comments? It might not actually be unsafe, given system() actually blocks on waitpid() which is specifically listed as a safe function. I'm a bit confused though, because system() generally sets the parent to ignore SIGINT which running the child process. That means the postmaster is being killed but pg_regress is not? If this is the case, then you won't be able to catch SIGINT anyway. The cases of interest are where the (new) code goes through spawn_process, which does a fork() and then calls system() in the child. So the intermediate child is probably SIGINT-blocked, but pg_regress itself isn't. I was planning to rewrite spawn_process anyway, because I noticed that as it's currently set up, we are actually creating four(!) processes per parallel test: the pg_regress child, the shell invoked by system, the psql invoked by the shell, and the connected backend. That's even worse than the shell script, which (at least on my system) used three processes. I believe we can get down to two (psql and backend) if spawn_process exec's the shell instead of using system, and also puts exec in front of the command string passed to the shell. So in that scenario there'd not be any signal-blocking going on anyway. That still leaves us with the question of whether pg_regress needs to do anything special when signaled, though. regards, tom lane ---(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: [HACKERS] password is no required, authentication is overridden
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Dunstan Sent: 19 July 2006 13:55 To: Hiroshi Saito Cc: Thomas Bley; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] password is no required, authentication is overridden I don't understand what you are saying here. The problem is that it is not clear (at least to the original user, and maybe to others) that when pgadmin3 saves a password it saves it where it will be found by all libpq clients, not just by pgadmin3. From: http://www.pgadmin.org/docs/1.4/connect.html If you select store password, pgAdmin stores passwords you enter in the ~/.pgpass file under *nix or %APPDATA%\postgresql\pgpass.conf under Win32 for later reuse. For details, see pgpass documentation. It will be used for all libpq based tools. If you want the password removed, you can select the server's properties and uncheck the selection any time. OK, although I am not sure I think that is sensible - it is at least documented. Does the dialog box also carry similar info? How is that optimal? If pgadmin3 were to save it in a non-standard location and then set PGPASSFILE to point to that location that would solve the problem. Or maybe it should offer a choice. Either way, how would a malicious user affect that? PGPASSFILE only contains a location, not the contents of the file, so exposing it is not any great security issue, as long as the location is itself protected. We have no sensible way of determining whether or not the libpq we are running with supports PGPASSFILE. Well, this answer is better. The lack of an API to tell you the library version is possibly worrying, though. cheers andrew ---(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
[HACKERS] How to refer to standard functions?
Is there any way to refer to standard functions when defining new functions? I tried AS '-','int4eq' but it just said ERROR: could not access file -: No such file or directory. It seems like a lot of data types would find it convenient if they have a representation that is similar to one of the standard data types. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgxs problem
Gregory Stark [EMAIL PROTECTED] writes: I've tracked down my problem with pgxs to Makefile.global in lib/pgxs/src. These lines seem to be the culprits: bindir := $(shell pg_config --bindir) datadir := $(shell pg_config --sharedir) sysconfdir := $(shell pg_config --sysconfdir) libdir := $(shell pg_config --libdir) pkglibdir := $(shell pg_config --pkglibdir) includedir := $(shell pg_config --includedir) pkgincludedir := $(shell pg_config --pkgincludedir) mandir := $(shell pg_config --mandir) docdir := $(shell pg_config --docdir) localedir := $(shell pg_config --localedir) I think it should be running $(pkglibdir)/bin/pg_config Your reasoning is circular. How are we to find out pkglibdir, if not by asking pg_config? (It's the wrong path anyway, since pkglibdir isn't where pg_config lives...) The documented behavior is that pgxs invokes whatever pg_config is in your PATH. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Progress bar updates
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: In practice, if a query is taking long enough for this feature to be interesting, making another connection and looking to see what's happening is not a problem, and it's likely to be the most practical way anyway for many clients. It would be the most practical way for a DBA to monitor an application. But it's not going to be convenient for clients like pgadmin or psql. [ shrug... ] Let me explain it to you this way: a progress counter visible through pg_stat_activity is something that might possibly get done in time for 8.2. If you insist on having the other stuff right off the bat as well, it won't get done this cycle. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well
Gregory Stark [EMAIL PROTECTED] writes: One possible criticism is that a user that manually does BEGIN; CLUSTER DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable. s/possible criticism/deal-breaker/ ... you can't possibly think that the above would be acceptable. It'd be worse than won't be undoable; it'd probably corrupt your database. regards, tom lane ---(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
[HACKERS] Patch process?
Just a general question - I submitted a patch for contrib/cube (adding a new function converting everything from V0 to V1), what is the process from here onwards? Do we have an active maintainer of this code? How is it reviewed? I would like to continue working on the cube stuff, as our company uses it heavily and has developed a core library of functions that we use regularly. I would love to be able to add these back to the Postgres project, but I'm not sure about what the guidelines are for contrib. I have noticed comments elsewhere about contrib packages being removed because they didn't follow guidelines, so I don't want to fall foul of that line, but I am not sure where the line is. I noticed that one of the regression tests now fail, as the original regression test expects a certain ordering from a query that doesn't request ordered results. Should I fix the test case so that ordering will be assured? Should I add test cases for most of the functions that currently aren't being tested? Thanks, Josh Reich ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] password is no required, authentication is overridden
-Original Message- From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: 19 July 2006 15:20 To: Dave Page Cc: Hiroshi Saito; Thomas Bley; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] password is no required, authentication is overridden From: http://www.pgadmin.org/docs/1.4/connect.html If you select store password, pgAdmin stores passwords you enter in the ~/.pgpass file under *nix or %APPDATA%\postgresql\pgpass.conf under Win32 for later reuse. For details, see pgpass documentation. It will be used for all libpq based tools. If you want the password removed, you can select the server's properties and uncheck the selection any time. OK, although I am not sure I think that is sensible - it is at least documented. Does the dialog box also carry similar info? It has a Help button that takes you straight to that doc page. We have no sensible way of determining whether or not the libpq we are running with supports PGPASSFILE. Well, this answer is better. The lack of an API to tell you the library version is possibly worrying, though. Indeed. The issue has been raised a few times in the past but for reasons I forget, such an API has never been added :-( I'd be happy with: int PQmajorVer() int PQminorVer() Or something similar. It would also be handy to have something like PQhasKerberos()... Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Statement Queuing
Something that would be extremely useful to add to the first pass of this would be to have a work_mem limiter. This would allow users to set work_mem much more aggressively without worrying about pushing the machine to swapping. That capability alone would make this valuable to a very large number of our users. On Mon, Jul 10, 2006 at 11:02:58AM +1200, Mark Kirkwood wrote: A while ago in connection with the 8.2 planning [1] there was some discussion of resource management and statement queuing [2]. I am currently looking at implementing a resource management/queuing module for Greenplum - initially targeting Bizgres, but I'm thinking it could be beneficial for non-Bizgres (i.e Postgresql) users too. There has been some discussion on the Bizgres list already [3] together with some WIP code [4] and a review [5]. the code is a little rough - representing my getting to grips with the standard lock mechanism in order to produce enough of a prototype to study the issues. In that light I would very much appreciate comments concerning the design itself and also feedback for the questions posted in the review [4] - either here, the Bizgres-general list or both. Here is a lightning overview of this whole resource queuing/scheduling business to hopefully put it in context (very short version of [3]): For certain workloads (particularly DSS and reporting), the usual controls (max_connections or a connection pool, work_mem etc) are not really enough to stop the situation where several simultaneously executing expensive queries temporarily cripple a system. This is particularly the case where user specified queries are permitted. What is needed is some way to throttle or queue these queries in some finer manner - such as (simple case) restricting the number of simultaneously executing queries, or restricting the total cost of all simultaneously executing queries (others are obviously possible, these are just the simplest). To make this work a new object - a resource queue is proposed, which holds limits and current counters for resources, plus a new sort of lock, something like a standard one, but instead of deterministic conflict rules based on lockmethod, a check on the counter/total for the relevant resource is performed instead. best wishes Mark [1] http://archives.postgresql.org/pgsql-hackers/2006-03/msg01122.php [2] http://archives.postgresql.org/pgsql-hackers/2006-03/msg00821.php [3] http://pgfoundry.org/pipermail/bizgres-general/2006-June/000492.html [4] http://homepages.paradise.net.nz/markir/download/bizgres/bizgres-resschedular-06-29.patch [5] http://lists.pgfoundry.org/pipermail/bizgres-general/2006-July/000521.html ---(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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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
Re: [HACKERS] url for TODO item, is it right?
Marc G. Fournier wrote: Why can't we just write a script that creates new numbers as needed, such as msg00163.1.php and msg00163.2.php? As far as I can tell, there is nothing magical about the naming schema itself that would cause such URLs to break anything. Agreed. It is nice to have the emails numbered in arrival order, but changes to old URLs are worse. 'k, so is the concensus here that I regenerate everything with the 'broken msg seperator', and then revert to the unbroken one for new stuff? its no sweat, I just fear this is going to re-crop up sometime in the future if we ever have to regenerate from the mbox files, as well have some in 'broken format' and some in the 'unbroken', but renumbering *then* will still affect everything ... Basically, we're just differing the headaches to a later date when we have no choice :( Well, ideally we could have the new items renumbered on to the end, starting at 10,000 or something. That way, the numbers aren't changed, but the missing items are now visible. Does the search system assume that numering is always increasing? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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: [HACKERS] How to refer to standard functions?
Gregory Stark [EMAIL PROTECTED] writes: Is there any way to refer to standard functions when defining new functions? Sure, but they're language INTERNAL, not C. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Patch process?
On 7/19/06, Joshua Reich [EMAIL PROTECTED] wrote: Just a general question - I submitted a patch for contrib/cube (adding a new function converting everything from V0 to V1), what is the process from here onwards? Do we have an active maintainer of this code? How is it reviewed? You should post into -patches@ list, then some core member will review and apply it. The review may take some time, you should not worry about that. I see that you already got general ACK. I would like to continue working on the cube stuff, as our company uses it heavily and has developed a core library of functions that we use regularly. I would love to be able to add these back to the Postgres project, but I'm not sure about what the guidelines are for contrib. I have noticed comments elsewhere about contrib packages being removed because they didn't follow guidelines, so I don't want to fall foul of that line, but I am not sure where the line is. The line mostly depends on quetions 'Is it useful?' and 'Is it active?' As you mentioned, it is useful and if you will fix problems then its also active. Seeing cvs log of the module, you could consider becoming the maintainer. I noticed that one of the regression tests now fail, as the original regression test expects a certain ordering from a query that doesn't request ordered results. Should I fix the test case so that ordering will be assured? Should I add test cases for most of the functions that currently aren't being tested? Sure, as a maintainer, you should fix all problems :) -- marko ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Patch process?
Joshua Reich [EMAIL PROTECTED] writes: Just a general question - I submitted a patch for contrib/cube (adding a new function converting everything from V0 to V1), what is the process from here onwards? Do we have an active maintainer of this code? It sounds like you've just acquired that position ;-) How is it reviewed? Same as everything else, pretty much: patches go to pgsql-patches and are (supposed to be) reviewed before being committed. If it's in the nature of a new feature rather than a simple bug fix or code cleanup, you might want to first start a discussion on pgsql-hackers --- if anyone has a better idea about how to do things, it's better to find it out before you start coding instead of after you finish. I noticed that one of the regression tests now fail, as the original regression test expects a certain ordering from a query that doesn't request ordered results. Should I fix the test case so that ordering will be assured? Should I add test cases for most of the functions that currently aren't being tested? Because we're relying so heavily on the buildfarm these days, failing regression tests are quite unacceptable. Adding an ORDER BY might be the best solution, or maybe you should just change the expected output --- do you understand exactly why the results changed? As for adding more tests, you can within reason --- don't make the running time enormous. regards, tom lane ---(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
Re: [HACKERS] Patch process?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marko Kreen Sent: 19 July 2006 16:13 To: Joshua Reich Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Patch process? On 7/19/06, Joshua Reich [EMAIL PROTECTED] wrote: Just a general question - I submitted a patch for contrib/cube (adding a new function converting everything from V0 to V1), what is the process from here onwards? Do we have an active maintainer of this code? How is it reviewed? You should post into -patches@ list, then some core member will review and apply it. Not core, a committer. Although some people are both, not all are. If that makes sense! Regards, Dave. ---(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
Re: [HACKERS] Patch process?
On 7/19/06, Dave Page dpage@vale-housing.co.uk wrote: You should post into -patches@ list, then some core member will review and apply it. Not core, a committer. Although some people are both, not all are. If that makes sense! Indeed. Obviously, non-committers can (and do) review patches. Just you need to get the attention of at least one committer to get it into CVS. -- marko ---(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: [HACKERS] poor performance with Context Switch Storm at TPC-W.
On Fri, Jul 14, 2006 at 02:58:36PM +0900, Katsuhiko Okano wrote: NOT occurrence of CSStorm. The value of WIPS was about 400. (but the value of WIPS fell about to 320 at intervals of 4 to 6 minutes.) If you haven't changed checkpoint timeout, this drop-off every 4-6 minutes indicates that you need to make the bgwriter more aggressive. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RESET CONNECTION?
Alvaro Herrera wrote: Bruce Momjian wrote: Mario Weilguni wrote: Will this patch make it into 8.2? http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php It's a really nice feature, would be extremly useful with tools like pgpool. No, it will not because RESET CONNECTION can mess up interface code that doesn't want the connection reset. We are not sure how to handle that. Hmm, what interface code are you talking about? I believe JDBC, for example, sets things inside the interface that would be broken by RESET CONNECTION. Here is a thread about it: http://archives.postgresql.org/pgsql-patches/2005-01/msg00029.php -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RESET CONNECTION?
Tatsuo Ishii wrote: I'm disappointed. Can you point out past discussion for this? Yes: http://archives.postgresql.org/pgsql-patches/2005-01/msg00029.php --- -- Tatsuo Ishii SRA OSS, Inc. Japan Mario Weilguni wrote: Will this patch make it into 8.2? http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php It's a really nice feature, would be extremly useful with tools like pgpool. No, it will not because RESET CONNECTION can mess up interface code that doesn't want the connection reset. We are not sure how to handle that. --- Am Freitag, 7. Juli 2006 19:13 schrieb Bruce Momjian: There are roughly three weeks left until the feature freeze on August 1. If people are working on items, they should be announced before August 1, and the patches submitted by August 1. If the patch is large, it should be discussed now and an intermediate patch posted to the lists soon. FYI, we don't have many major features ready for 8.2. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Patch process?
Tom Lane wrote: Joshua Reich [EMAIL PROTECTED] writes: Do we have an active maintainer of this code? It sounds like you've just acquired that position ;-) More than happy to take the role. How is it reviewed? Same as everything else, pretty much: patches go to pgsql-patches and are (supposed to be) reviewed before being committed. If it's in the nature of a new feature rather than a simple bug fix or code cleanup, you might want to first start a discussion on pgsql-hackers --- if anyone has a better idea about how to do things, it's better to find it out before you start coding instead of after you finish. Ok. I'll keep changes to a minimum; before I add features, I'll discuss here. Because we're relying so heavily on the buildfarm these days, failing regression tests are quite unacceptable. Adding an ORDER BY might be the best solution, or maybe you should just change the expected output --- do you understand exactly why the results changed? As for adding more tests, you can within reason --- don't make the running time enormous. Ok. I'll fix the test cases so that everything is hunky dory. Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Patch process?
Dave Page wrote: You should post into -patches@ list, then some core member will review and apply it. Not core, a committer. Although some people are both, not all are. If that makes sense! I have raised this issue before: I don't believe committers are identified as such on the web site, and they (we) probably should be. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Progress bar updates
On Wednesday 19 July 2006 07:33, Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: In practice, if a query is taking long enough for this feature to be interesting, making another connection and looking to see what's happening is not a problem, and it's likely to be the most practical way anyway for many clients. It would be the most practical way for a DBA to monitor an application. But it's not going to be convenient for clients like pgadmin or psql. [ shrug... ] Let me explain it to you this way: a progress counter visible through pg_stat_activity is something that might possibly get done in time for 8.2. If you insist on having the other stuff right off the bat as well, it won't get done this cycle. Having the progress, or estimated time of completion in pg_stat_activity sounds like a good starting point, the rest of the desired features can be bolted on top of this down the road regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Loading the PL/pgSQL debugger (and other plugins)
I'm working on a patch that implements the PL/pgSQL instrumentation
stuff (i.e. the PL/pgSQL debugger) that I discussed at the Anniversary
Summit and I need some opinions (this seems like a good place to look
for opinions :-)
A quick review: the PL/pgSQL debugger is designed as an optional
plugin that loads into the PL/pgSQL interpreter on-demand. You can
use the plugin idea to implement other kinds of instrumentation (I
demo'ed a tracer and a profiler at the conference, along with a
debugger). A plugin architecture greatly reduces the (source code)
footprint that would normally be required to implement a full-featured
debugger.
A plugin is basically a structure that contains a few function
pointers. If those function pointers are NULL, the PL/pgSQL interpreter
works exactly the way it does today. If any of those function pointers
are non-NULL, the PL/pgSQL interpreter calls the target function (which
points to a chunk of code inside of the plugin) and the plugin does
whatever it needs to do.
Right now, the plugin structure looks like this:
typedef struct
{
void (*init)( estate, func, error_callback, assign_expr, expr );
void (*func_beg)( PLpgSQL_execstate * estate, PLpgSQL_function * func );
void (*func_end)( PLpgSQL_execstate * estate, PLpgSQL_function * func );
void (*stmt_beg)( PLpgSQL_execstate * estate, PLpgSQL_stmt * stmt );
void (*stmt_end)( PLpgSQL_execstate * estate, PLpgSQL_stmt * stmt );
} PLpgSQL_plugin;
I've truncated the argument list (in this e-mail) for the (*init)()
function since it's rather long (error_callback and assign_expr are both
function pointers).
When the PL/pgSQL intrepreter loads the plugin, it calls the
plugin-init() function.
When the PL/pgSQL intrepreter starts running a new function, it calls
the plugin-func_beg() function.
When the PL/pgSQL intrepreter completes a function, it calls the
plugin-func_end() function.
When the PL/pgSQL interpreter is about to execute a line of PL/pgSQL
code, it calls plugin-stmt_beg()
When the PL/pgSQL interpreter has finished executing a line of PL/pgSQL
code, it calls plugin-stmt_end()
So here is where I need a few opinions:
1) I think the most straightforward way to load an instrumentation
plugin is to define a new custom GUC variable (using the
custom_variable_classes mechanism). When the PL/pgSQL call-handler
loads, it can check that config. variable (something like plpgsql.plugin
= '$libdir/plugin_profiler' or plpgsql.plugin =
'$libdir/plugin_debugger') and load the plugin if non-NULL. That seems
a little obtuse to me since custom variables don't appear in the
prototype postgresql.conf file. Would it be better to add a real GUC
variable instead of a custom variable?
2) Given that plpgsql.plugin points to the name of a shared-object file
(or DLL or whatever you prefer to call it), we need to find *something*
inside of the file. The most obvious choice would be to look for a
variable (a structure or structure pointer) with a fixed name. That
would mean, for example, that a plugin would define an externally
visible PLpgSQL_plugin structure named plugin_hooks and the PL/pgSQL
interpreter would look for that symbol inside of the plugin.
Alternatively, we could look for a function inside of the plugin
(something like 'plugin_loader') and then call that function with a
pointer to a PLpgSQL_plugin structure. I prefer the function-pointer
approach since we already have a reliable mechanism in place for finding
a function inside of a shared-object (the same mechanism works for
finding a variable instead of a function pointer, but I doubt that that
has been tested in all platforms).
3) Any comments on the PLpgSQL_plugin structure? Should it include (as
it's first member) a structure version number so we can add to/change
the structure as needed?
4) Do we need to support multiple active plugins? Would you ever need
to load the debugger at the same time you've loaded the profiler (no)?
Would you ever need to load the tracer at the same time you need the
debugger (probably not)? If we need to support multiple plugins, should
be just introduce a meta-plugin that knows how to handle a list of other
plugins? (Messy, but certainly gets the job done without worrying about
it right now).
5) I'll also be adding a void pointer to the PLpgSQL_execstate structure
(think of a PLpgSQL_execstate as a stack frame). The new pointer is
reserved for use by the plugin. It may be handy to add a void pointer
to each PLpgSQL_stmt as well - is that acceptable? (That would mean an
extra 4-bytes per-line of compiled PL/pgSQL code, even if you don't have
a plugin loaded).
Any other comments? Obviously, you'll have a chance to critique the
patch when I get it sent in.
Thanks for your help.
-- Korry
---(end of broadcast)---
TIP 4: Have you searched our list archives?
Re: [HACKERS] pg_regress breaks on msys
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: pg_regress now seems to break on Msys virtual locations: Example from the buildfarm: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=snakedt=2006-07-19%2009:00:00 == pgsql.4660/src/test/regress/log/initdb.log === The filename, directory name, or volume label syntax is incorrect. Surely this was tested when the original was prepared? You can probably blame me instead of Magnus, because I did extensive fooling with the quoting of the commands issued by pg_regress ... and no, I don't have msys to test with, that's what the buildfarm is for ;-) Neither do I right now. This error message seems pretty thoroughly unhelpful though. Any ideas what it's unhappy about? I think we need to change the pg_regress error messages so that it includes the command string that failed, at least for now. Then we might know instead of speculating. It will be either quoting problem or a vitual path problem, I am pretty sure. The old shell script ran in a bourne-shell-like manner. But calling system() from a C program will call the Windows command shell, where the quoting rules are quite different. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Continuous dataflow streaming
On Mon, Jul 17, 2006 at 09:25:49AM -0700, Josh Berkus wrote: Dragan, What are the possibilities (if any) for continuous dataflow streaming with PostgreSQL v.8.1 ? Something like TelegraphCQ project,but it was for v.7.3.Is there any alternatives for the latest version of PostgreSQL ? The TelegraphCQ team has stopped public development. So it's pretty much waiting for someone to take on their code. FWIW, the existing version of TCQ never solved the not crashing problem, let alone integration with transactional tables. Also, Neil Conway gave a talk about this at the conference, which unfortunately I couldn't attend. I talked to him afterwards though, and he's definately interested in getting streaming support for PostgreSQL in some fashion. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Windows 2000 Support
Hi, I just finished setting up a new buildfarm member (Bandicoot) running Windows 2000 Pro. Aside from the fact that it now fails with the same cyptic pg_regress error as seen on Snake, it also became apparent that CVS HEAD won't run properly on an unpatched Windows 2000 (initdb - and probably pg_ctl - fails when trying to dynamically load advapi32.dll which is used to shed excess privileges). This was solved by the installation of service pack 4. Unfortunately I couldn't find a way to catch the error - it seems to kill the app and throw a messagebox with a cryptic message. Given that you have to be clinically insane to run Win2K without patching it to the hilt I'm not overly concerned by this (and will add appropriate checks to pgInstaller), but it's probably worth mentioning that our minimum supported platform is Windows 2000 Pro with Service Pack 4 from 8.2. Regards, Dave. ---(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: [HACKERS] pg_regress breaks on msys
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: This error message seems pretty thoroughly unhelpful though. Any ideas what it's unhappy about? I think we need to change the pg_regress error messages so that it includes the command string that failed, at least for now. Done, but I bet it doesn't tell us anything we don't know already. It will be either quoting problem or a vitual path problem, I am pretty sure. The old shell script ran in a bourne-shell-like manner. But calling system() from a C program will call the Windows command shell, where the quoting rules are quite different. In src/include/port.h we have /* * Win32 needs double quotes at the beginning and end of system() * strings. If not, it gets confused with multiple quoted strings. * It also requires double-quotes around the executable name and * any files used for redirection. Other args can use single-quotes. * * See the Notes section about quotes at: * http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM */ The referenced link seems to be dead :-( but AFAICS the pg_regress code is following the stated rules. Also, how is it getting past the make install step which is quoting things just the same? Puzzling. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] How to refer to standard functions?
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: Is there any way to refer to standard functions when defining new functions? Sure, but they're language INTERNAL, not C. ah, thanks -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: One possible criticism is that a user that manually does BEGIN; CLUSTER DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable. s/possible criticism/deal-breaker/ ... you can't possibly think that the above would be acceptable. It'd be worse than won't be undoable; it'd probably corrupt your database. I'm not sure I understand why. Or are you just referring to the snapshot bugs in cluster? I'm imagining what would happen is that cluster would take the liberty of committing the transaction begun by the BEGIN since it hadn't been used yet anyways. Then it would leave you with a fresh transaction when it was done so the rollback would be a noop as it just rolled back that empty transaction. I do have an alternative idea: Instead of having psql parse the SQL commands to try to guess which commands are non-transactional, have psql simply try the command, and check the error code. If a command fails immediately after the BEGIN implicitly inserted when autocommit=false and it fails with a specific error code set aside for this purpose, then abort the transaction and reattempt it outside a transaction. If that error comes back during a user-initiated transaction or with autocommit=true then psql wouldn't do anything special. I'm still a bit bothered by all this since I think it would still make it hard to use non-transactional commands from other clients. Clients like DBI and JDBC generally assume you're *always* in a transaction so one imagines they do something similar to psql with inserting implicit BEGINs everywhere. The real solution is probably to go back to autocommit=false semantics on the server and have psql implement autocommit mode simply by inserting commit all the time. But I have a feeling people are so burned by the last change in this area that bringing it up again isn't going to win me any friends :) -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: s/possible criticism/deal-breaker/ ... you can't possibly think that the above would be acceptable. It'd be worse than won't be undoable; it'd probably corrupt your database. I'm not sure I understand why. Or are you just referring to the snapshot bugs in cluster? The ROLLBACK would undo the catalog updates made by the command, but not its non-transactional changes. Possibly in some cases there would be no resulting inconsistency, but in general it would leave inconsistent state. In most of our usages of PreventTransactionChain, the point is that a rollback occuring after the command thinks it's completed would be unsafe. I do have an alternative idea: Instead of having psql parse the SQL commands to try to guess which commands are non-transactional, have psql simply try the command, and check the error code. I don't think it is really all that hard to have psql parse the commands far enough to determine if they're transactional or not. If you can't tell by easy visual inspection which kind of command it is, then we've misdesigned the command language and should change it: it'll be confusing for people as well as programs. In the case of the online-index-creation command, this may require putting the critical keyword up front rather than burying it somewhere in the command string, but I don't see a problem with that. regards, tom lane ---(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: [HACKERS] pg_regress breaks on msys
Bruce Momjian [EMAIL PROTECTED] writes: * From http://www.computerhope.com/cmd.htm: * * 1. If all of the following conditions are met, then quote characters * on the command line are preserved: * * - no /S switch * - exactly two quote characters * - no special characters between the two quote characters, where special * is one of: ()@^| * - there are one or more whitespace characters between the the two quote * characters * - the string between the two quote characters is the name of an * executable file. Hmm, that suggests that our code works *only* if there's white space in all the paths !? Seems unlikely that this description is fully correct, or we'd have had problems before. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Progress bar updates
Neil Conway wrote: I would suggest starting with utility functions like index builds or COPY which would have to be specially handled anyways. Handling all optimizable queries in a single generic implementation seems like something to tackle only once the basic infrastructure is there and working for simple cases. Of course the estimates would be not much better than guesses. Estimating query progress for DDL should be reasonably doable, but I think it would require some hard thought to get even somewhat accurate estimates for SELECT queries -- and I'm not sure there's much point doing this if we don't at least have an idea how we might implement reasonably accurate progress reporting for every kind of query. We already have EXPLAIN ANALYZE. Perhaps the right way to do this is something that provides similar output. I could see something that looks like EXPLAIN for the parts that have not yet executed, something reasonable to show progress of the currently active part of the plan (current time, rows, loops), and EXPLAIN ANALYZE output for the parts which have been completed. I can see how this might lead to dynamically re-planning queries. Going backwards, perhaps there's something related to progress monitoring that could be taken from the TelegraphCQ work? Drew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Loading the PL/pgSQL debugger (and other plugins)
korry [EMAIL PROTECTED] writes: I'm working on a patch that implements the PL/pgSQL instrumentation stuff (i.e. the PL/pgSQL debugger) that I discussed at the Anniversary Summit and I need some opinions (this seems like a good place to look for opinions :-) Opinions R US ;-) 1) I think the most straightforward way to load an instrumentation plugin is to define a new custom GUC variable (using the custom_variable_classes mechanism). This seems a bit messy and special-purpose. I see no good reason to tie it to plpgsql; we'll just need another one for every other language. IMHO what we want is something with similar properties to preload_libraries, but processed on a per-backend basis instead of once at postmaster start. (You could almost just tell people to select the plugin they want by LOADing it, but that is hard to use if you're trying to debug a non-interactive application. A GUC variable can be set for an app without much cooperation from the app.) When the plugin's shared library gets loaded, one way or the other, it should construct the function-pointer struct and then pass it to a function defined by plpgsql (this lets us hide/postpone the decision about whether there can be more than one active plugin). One issue that needs to be thought about with either this proposal or your original is what permissions are needed to set the GUC variable. I don't think we dare allow non-superusers to specify LOADing of arbitrary shared libraries, so there has to be some filter function. Perhaps a better way is that the GUC variable specifies a (list of) initialization functions to call at backend start, and then the superuserness is involved with installing the init functions into pg_proc, and the GUC variable itself needs no special permissions. Again, a plugin's init function would just register its function-pointer struct with plpgsql. We should also think about a deregistration function. This would allow you to turn debugging on and off within an interactive session. The GUC variable is really only for coercing non-interactive applications into being debuggable --- I don't see it as being important for interactive debugging, as compared to just select plugin_init(); ... 3) Any comments on the PLpgSQL_plugin structure? Should it include (as it's first member) a structure version number so we can add to/change the structure as needed? Given our current plans for enforcing recompiles at major version changes (via magic-block checking), I'm not sure I see a need for this. 4) Do we need to support multiple active plugins? Probably, but let's fix the API to hide this, so we don't have to commit now. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plPHP and plRuby
Marc G. Fournier wrote: src/pl/plphp/README.TXT src/pl/pljava/README.TXT src/pl/plj/README.TXT and anybody looking for pl-s would find the info in a logical place *That* idea I like ... Why don't we just reorganize our tree like that: everything/databases/postgresql/src/... everything/databases/mysql/README.txt everything/kernels/freebsd/README.txt everything/kernels/linux/README.txt ... That will make it much easier for people to set up their systems. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plPHP and plRuby
Hannu Krosing wrote: So we would have src/pl/plphp/README.TXT src/pl/pljava/README.TXT src/pl/plj/README.TXT and anybody looking for pl-s would find the info in a logical place Right. When was the last time any user looked under src/pl in the first place? Or even under src? If you're looking for pljava, it's the first hit in Google. I think people need to relax more. We are not making statements about language preferences -- making that claim is just paranoia. We are not missing the enterprise train, and there might be just as many people moving from PHP to Java, or we might just be making this up because no one can count that anyway. And we are not going to educate any Rail users, because people don't like to be lectured to if they didn't ask for it. The organization of the source code is controlled by exactly two factors: 1. history 2. convenience of development Anything else is between you and your packager. And if that didn't convince you, I still got PL/sh in the wait ... -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_regress breaks on msys
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: * From http://www.computerhope.com/cmd.htm: * * 1. If all of the following conditions are met, then quote characters * on the command line are preserved: * * - no /S switch * - exactly two quote characters * - no special characters between the two quote characters, where special * is one of: ()@^| * - there are one or more whitespace characters between the the two quote * characters * - the string between the two quote characters is the name of an * executable file. Hmm, that suggests that our code works *only* if there's white space in all the paths !? Seems unlikely that this description is fully correct, or we'd have had problems before. It is saying _all_ these have to be true, and we already quote executables, and the string, so we always have more than two quotes: * Win32 needs double quotes at the beginning and end of system() * strings. If not, it gets confused with multiple quoted strings. * It also requires double-quotes around the executable name and * any files used for redirection. Other args can use single-quotes. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plPHP and plRuby
Peter Eisentraut [EMAIL PROTECTED] writes: And if that didn't convince you, I still got PL/sh in the wait ... It seems like there may be enough interest in PL/Ruby to justify including it in our distro, but after taking a look at the package I can see a couple of pretty serious objections: 1. Wrong license. Unless the author can be persuaded to relicense as straight BSD, this discussion is a waste of time. 2. Coding style. The man does not believe in comments; do we really think anyone else is going to be able to maintain his work? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible Typecasting Bug with coalesce()
You are correct, Andreas.
= select now()'Jul 14 2006 9:16:47AM';
?column?
--
t
(1 row)
= select now() CASE WHEN 'Jul 14 2006 9:16:47AM' IS NOT NULL THEN
'Jul 14 2006 9:16:47AM' END;
?column?
--
f
(1 row)
I've also found that there must be more than one typecasting function
being used because the result can be different depending on the format
of the timestamp string:
= select now()coalesce('Jul 14 2006 9:16:47AM');
?column?
--
f
(1 row)
= select now()coalesce('2006-07-14 9:16:47');
?column?
--
t
(1 row)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_regress breaks on msys
Bruce Momjian [EMAIL PROTECTED] writes: Hmm, that suggests that our code works *only* if there's white space in all the paths !? Seems unlikely that this description is fully correct, or we'd have had problems before. It is saying _all_ these have to be true, and we already quote executables, and the string, so we always have more than two quotes: Well, the description is about as clear as mud, because it's not saying which two quote characters it's talking about. I read it as talking about the two quote characters around any one word/pathname. If you think it's talking about the two quote characters we put around the whole command (the SYSTEMQUOTE dodge), then we're certainly going to fail the no special characters test, because all these commands use I/O redirection symbols. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_regress breaks on msys
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: This error message seems pretty thoroughly unhelpful though. Any ideas what it's unhappy about? I think we need to change the pg_regress error messages so that it includes the command string that failed, at least for now. Done, but I bet it doesn't tell us anything we don't know already. Well, we have a result, courtesy of a special run from Stefan: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=seahorsedt=2006-07-19%2017:52:41 has: Command was: C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/bin/initdb -D C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/data -L C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/share/postgresql --noclean --no-locale ./log/initdb.log 21 The second C:/msys/1.0/ should not be in the path to initdb. Not sure how to fix. cheers andrew It will be either quoting problem or a vitual path problem, I am pretty sure. The old shell script ran in a bourne-shell-like manner. But calling system() from a C program will call the Windows command shell, where the quoting rules are quite different. In src/include/port.h we have /* * Win32 needs double quotes at the beginning and end of system() * strings. If not, it gets confused with multiple quoted strings. * It also requires double-quotes around the executable name and * any files used for redirection. Other args can use single-quotes. * * See the Notes section about quotes at: * http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM */ The referenced link seems to be dead :-( but AFAICS the pg_regress code is following the stated rules. Also, how is it getting past the make install step which is quoting things just the same? Puzzling. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_regress breaks on msys
After looking at the presumably-working uses of system() in initdb and pg_ctl, I have a theory about the pg_regress problem --- could it be that Windows system() requires a space between I/O redirection symbols and pathnames? I see that the pre-existing code consistently puts one, except in cases like 21: snprintf(cmd, MAXPGPATH, %s\%s\ %s%s \%s\ \%s\ 21 %s, SYSTEMQUOTE, postgres_path, pgdata_opt, post_opts, DEVNULL, log_file, SYSTEMQUOTE); but there's nothing in our docs saying this is necessary ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
Phil Frost wrote: On Wed, Jul 12, 2006 at 06:09:31PM -0400, Bruce Momjian wrote: Phil Frost wrote: On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote: Updated text: For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to quotelook up/ objects within the schema. Without this permission, it is still possible to see the object names by querying the system tables, but they cannot be accessed via SQL. No, this still misses the point entirely. See all my examples in this thread for ways I have accessed objects without usage to their schema with SQL. OK, well we are not putting a huge paragraph in there. Please suggest updated text. Well, if you won't explain the whole situation, nor change it, then all you can really say is it doesn't really work always. How about this: For schemas, allows access to objects contained in the specified schema. Note that the converse is not true in many cases: revoking usage on a schema is not sufficient to prevent access in all cases. There is precedent for new ways to bypass this check being added in future releases. It would be unwise to give this privilege much security value. Updated text: For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to quotelook up/ objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables, so this is not a completely secure way to prevent object access. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Loading the PL/pgSQL debugger (and other plugins)
Thanks for the quick feedback.
1) I think the most straightforward way to load an instrumentation
plugin is to define a new custom GUC variable (using the
custom_variable_classes mechanism).
This seems a bit messy and special-purpose.
Agreed, I'm not crazy about using a custom_variable_class variable
either.
I see no good reason to tie
it to plpgsql; we'll just need another one for every other language.
Hmmm... but the plugins themselves would be language-specific. I can't
imagine that a plugin (say a profiler) for PL/python would work for
PL/pgSQL. It seems to me that, even if we come up with a common
mechanism, we'll still need a separate GUC variable *name* for each
PL. Or am I not understanding something? Can you post an example of
what you are thinking (what would such a GUC variable look like)?
IMHO what we want is something with similar properties to preload_libraries,
but processed on a per-backend basis instead of once at postmaster start.
(You could almost just tell people to select the plugin they want by
LOADing it, but that is hard to use if you're trying to debug a
non-interactive application. A GUC variable can be set for an app
without much cooperation from the app.)
Agreed.
When the plugin's shared library gets loaded, one way or the other,
it should construct the function-pointer struct and then pass it to a
function defined by plpgsql (this lets us hide/postpone the decision
about whether there can be more than one active plugin).
But there's a timing issue there. If you ask the plugin to call a
call-handler function, then you can't load the plugin at backend
startup because the PL/pgSQL call-handler isn't loaded until it's
required. Since both the plugin and the call-handler are dynamically
loaded, I think one of them has to load the other. We already have a
mechanism for loading call-handlers on demand - it seems kind of messy
to introduce another mechanism for loading plugins (that in turn load
the call-handlers).
The PL/pgSQL call-handler has a convenient initialization function that
could read the GUC variable and load the referenced plugin (that's what
I'm doing right now).
What I'm thinking is that the plpgsql_init() function would look
something like this (my changes in red);
PLpgSQL_plugin pluginHooks;
typedef void (*plugin_loader_func)(PLpgSQL_plugin *hooks);
void
plpgsql_init(void)
{
static char * pluginName;
plugin_load_func plugin_loader();
/* Do initialization only once */
if (!plpgsql_firstcall)
return;
plpgsql_HashTableInit();
RegisterXactCallback(plpgsql_xact_cb, NULL);
plpgsql_firstcall = false;
/* Load any instrumentation plugins */
DefineCustomStringVariable( "plpgsql.plugin",
"Name of instrumentation plugin to use
when PL/pgSQL function is invoked",
NULL,
pluginName,
PGC_USERSET,
NULL,
NULL );
EmitWarningsOnPlaceholders("plpgsql");
if (pluginName )
{
plugin_loader = (plugin_loader_func
*)load_external_function(pluginName, "plugin_loader", false, NULL );
if (plugin_loader)
(*plugin_loader)(pluginHooks);
}
}
(Ignore the custom variable stuff for now)
Each plugin would export a plugin_loader() function - that function,
given a pointer to a PLpgSQL_plugin structure, would fill in that
structure with the required function pointers.
One issue that needs to be thought about with either this proposal or
your original is what permissions are needed to set the GUC variable.
I don't think we dare allow non-superusers to specify LOADing of
arbitrary shared libraries, so there has to be some filter function.
Perhaps a better way is that the GUC variable specifies a (list of)
initialization functions to call at backend start, and then the
superuserness is involved with installing the init functions into
pg_proc, and the GUC variable itself needs no special permissions.
Again, a plugin's init function would just register its function-pointer
struct with plpgsql.
You're right, privileges are an issue. Is it safe enough if we force
all plugins to reside in $libdir? Each plugin could enforce additional
security as needed that way, but you'd have to hold enough privileges
to get your plugin into $libdir to begin with so you can't write your
own nasty plugin to gain more privileges than you ought to have.
We should also think about a deregistration function. This would allow
you to turn debugging on and off within an interactive session. The
GUC variable is really only for coercing non-interactive applications
into being debuggable --- I don't see it as being important for
interactive debugging, as compared to just "select plugin_init();" ...
Ok.
3) Any comments on the PLpgSQL_plugin structure? Should it include (as
it's first member) a structure version number so we can add to/change
the structure as needed?
Given our current plans for enforcing recompiles at major version
changes (via magic-block checking), I'm
Re: [HACKERS] pg_regress breaks on msys
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Hmm, that suggests that our code works *only* if there's white space in all the paths !? Seems unlikely that this description is fully correct, or we'd have had problems before. It is saying _all_ these have to be true, and we already quote executables, and the string, so we always have more than two quotes: Well, the description is about as clear as mud, because it's not saying which two quote characters it's talking about. I read it as talking about the two quote characters around any one word/pathname. If you think it's talking about the two quote characters we put around the whole command (the SYSTEMQUOTE dodge), then we're certainly going to fail the no special characters test, because all these commands use I/O redirection symbols. Right, the top says: * 1. If all of the following conditions are met, then quote characters * on the _command_ _line_ are preserved: It is talking about the entire command string, because this is system() and there is no distinction between commands and arguments --- it is one big string. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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: [HACKERS] pg_regress breaks on msys
Andrew Dunstan [EMAIL PROTECTED] writes: Command was: C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/bin/initdb -D C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/data -L C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/share/postgresql --noclean --no-locale ./log/initdb.log 21 The second C:/msys/1.0/ should not be in the path to initdb. Ah-hah, so apparently make install DESTDIR=foo somehow inserts DESTDIR after that instead of before it? What we need is a way to determine the paths that make install used ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_regress breaks on msys
Andrew Dunstan [EMAIL PROTECTED] writes: Well, we have a result, courtesy of a special run from Stefan: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=seahorsedt= 2006-07-19%2017:52:41 has: Command was: C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test /regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbu ild/HEAD/inst/bin/initdb -D C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/ regress/./tmp_check/data -L C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/ regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbui ld/HEAD/inst/share/postgresql --noclean --no-locale ./log/initdb.log 21 The second C:/msys/1.0/ should not be in the path to initdb. Andrew's on to something, I think. Colons are verboten anywhere in a filename except position 2, right after a drive letter. The path to postgresql later in the command will also have problems. Regards, Paul Bort ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plPHP and plRuby
Peter Eisentraut wrote: Hannu Krosing wrote: So we would have src/pl/pljava/README.TXT and anybody looking for pl-s would find the info in a logical place Right. When was the last time any user looked under src/pl in the first place? Or even under src? If you're looking for pljava, it's the first hit in Google. The difference is that I will have reasonable confidence that the README.TXT under src/pl will give instructions that match the version of PostgreSQL that I have. I assume that README will call out the version of PL/R or PL/Ruby that I want that was tested with the release of PostgreSQL I have. The first hit on Google will probably give me the most recently blogged about version; which does nothing to help me find what I need. The organization of the source code is controlled by exactly two factors: 2. convenience of development I thought convenience of development included the addressing the problem that PLs are annoyingly deeply tied to specific versions of Core. I would imagine with this README.TXT proposal, it's the responsibility of the PL/XXX developer to port their PL to PostgreSQL during the Beta, and if the did and tested it, the release will point to the version of the PL supported by the PL maintainer for that version. If they don't do this testing during the beta, the README.TXT may merely say the PL/Haskell team did not complete testing during the 8.2 beta; so good luck. This aids to the convenience of development of PostgreSQL and the PLs because it defines the process and responsibility for integration testing the PLs with the Core releases; and puts some pressure to synchronize the releases. Anything else is between you and your packager. And if that didn't convince you, I still got PL/sh in the wait ... With which versions of PostgreSQL is this version of PL/sh supported? I see that PL/sh on http://pgfoundry.org/projects/plsh/ is version 1.1? Does that mean it goes with PostgreSQL 1.1? The Projects page for PL/SH (http://plsh.projects.postgresql.org/) suggests it was last modified in May 2005 - does that mean I need the May 2005 backend of PostgreSQL to compile it? Oh. The download page says older releases are also supported. Does that include 7.1? Putting this info in the README.TXT is one way to help users know what's supported. If I saw a README.TXT for pl/sh I'd have some confidence I'd be directly pointed to the version I need. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_regress breaks on msys
I wrote: Ah-hah, so apparently make install DESTDIR=foo somehow inserts DESTDIR after that instead of before it? What we need is a way to determine the paths that make install used ... AFAICS, the makefiles are just blindly concatenating DESTDIR with bindir etc, for instance this is how initdb/Makefile installs initdb: $(INSTALL_PROGRAM) initdb$(X) '$(DESTDIR)$(bindir)/initdb$(X)' The evidence at hand says that this should produce exactly the same path string as pg_regress is then using to call initdb. So the question in my mind now is how come the make install step isn't failing. For that matter, this same path-construction technique was used by the shellscript... so how come it worked before? It would be simple enough to make pg_regress strip off a drive letter from the path strings it receives from the Makefile, but I'm not seeing a principled way to discover that the /msys/1.0/ part has to go. How are the makefiles managing to generate a different value of $(bindir) at install time than what was passed into pg_regress at build time? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Index corruption
For the record, here are the results of our (ongoing) inevstigation into the index/heap corruption problems I reported a couple of weeks ago. We were able to trigger the problem with kernels 2.6.16, 2.6.17 and 2.6.18.rc1, with 2.6.16 seeming to be the most flaky. By replacing the NFS-mounted netapp with a fibre-channel SAN, we have eliminated the problem on all kernels. From this, it would seem to be an NFS bug introduced post 2.6.14, though we cannot rule out a postgres bug exposed by unusual timing issues. Our starting systems are: Sun v40z 4 x Dual Core AMD Opteron(tm) Processor 875 Kernel 2.6.16.14 #8 SMP x86_64 x86_64 x86_64 GNU/Linux (and others) kernel boot option: elevator=deadline 16 Gigs of RAM postgresql-8.0.8-1PGDG Bonded e1000/tg3 NICs with 8192 MTU. Slony 1.1.5 NetApp FAS270 OnTap 7.0.3 Mounted with the NFS options rw,nfsvers=3,hard,rsize=32768,wsize=32768,timeo=600,tcp,noac Jumbo frames 8192 MTU. All postgres data and logs are stored on the netapp. All tests results were reproduced with postgres 8.0.8 __ Marc On Fri, 2006-06-30 at 23:20 -0400, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: We tried all of these suggestions and still get the problem. Nothing interesting in the log file so I guess the Asserts did not fire. Not surprising, it was a long shot that any of those things were really broken. But worth testing. We are going to try experimenting with different kernels now. Unless anyone has any other suggestions. Right at the moment I have no better ideas :-( regards, tom lane signature.asc Description: This is a digitally signed message part
Re: [HACKERS] plPHP and plRuby
Ron Mayer [EMAIL PROTECTED] writes: Peter Eisentraut wrote: Right. When was the last time any user looked under src/pl in the first place? Or even under src? If you're looking for pljava, it's the first hit in Google. The difference is that I will have reasonable confidence that the README.TXT under src/pl will give instructions that match the version of PostgreSQL that I have. I assume that README will call out the version of PL/R or PL/Ruby that I want that was tested with the release of PostgreSQL I have. On what do you base that assumption? A README file laying about in an otherwise unused part of the source tree is the very definition of out of sight, out of mind. I can pretty much guarantee you that it will NOT get updated, especially not during minor releases. Even if it is up to date at the instant we put out a release, it'll be obsolete as soon as the external project makes an update release. ISTM links like this are far better kept on project websites ... I would imagine with this README.TXT proposal, it's the responsibility of the PL/XXX developer to port their PL to PostgreSQL during the Beta, and if the did and tested it, the release will point to the version of the PL supported by the PL maintainer for that version. And if they didn't? I was just noticing that the current release of plruby contains installation instructions that appear to date to 7.3. If he can't be bothered to update his own docs, what are the odds that he'll submit timely updates for a README in the main source tree? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_regress breaks on msys
Tom Lane wrote: I wrote: Ah-hah, so apparently make install DESTDIR=foo somehow inserts DESTDIR after that instead of before it? What we need is a way to determine the paths that make install used ... AFAICS, the makefiles are just blindly concatenating DESTDIR with bindir etc, for instance this is how initdb/Makefile installs initdb: $(INSTALL_PROGRAM) initdb$(X) '$(DESTDIR)$(bindir)/initdb$(X)' The evidence at hand says that this should produce exactly the same path string as pg_regress is then using to call initdb. So the question in my mind now is how come the make install step isn't failing. For that matter, this same path-construction technique was used by the shellscript... so how come it worked before? It would be simple enough to make pg_regress strip off a drive letter from the path strings it receives from the Makefile, but I'm not seeing a principled way to discover that the /msys/1.0/ part has to go. How are the makefiles managing to generate a different value of $(bindir) at install time than what was passed into pg_regress at build time? regards, tom lane I think we'll need to have the makefile tell us what it thinks the cwd is, so if it's a virtual path we'll be able to use that. Compare the install log on the 8.1 branch (from our new buildfarm member bandicoot) here http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=bandicootdt=2006-07-19%2009%3A52%3A28stg=check with what seahorse is showing. Note that the install does not involve windows paths at all - just Msys virtual paths. But we do need to use Windows paths for the data files. cheers andrew ---(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: [HACKERS] lastval exposes information that currval does not
On Wed, Jul 19, 2006 at 02:42:49PM -0400, Bruce Momjian wrote: Updated text: For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to quotelook up/ objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables, so this is not a completely secure way to prevent object access. I think you are not understanding the nature of the problem I have described. It is just not the names that can be accessed in the absence of usage on a schema, it is *the content of the relations*. It is obvious to anyone who has ever looked in pg_* that metadata is not hidden by any amount of permission twiddling with grant and revoke. This isn't great from a security standpoint, but at least it's documented and expected, so one can design around it. However, the usage on schema privilege has undocumented, unexpected behavior. One would think from the documentation and from experimentation that one can not exercise any privileges on an object (excepting what can be done through the system catalogs) without having usage on the schema that contains it. However, this is not always the case! If you look at my previous posts, I have repeatedly demonstrated ways to access objects (not just the names or metadata, but the _full_ _contents_) contained in a schema to which one does not have the 'usage' privilege. The developers must consider this a feature, because none have acknowledged it as a security bug. This being the case, it is important that people be advised that the schema usage privilege does not always control access to contained objects, and that the ways which it can be bypassed are perhaps not numerous, but definitely subtle, and thus likely to escape security audits and later be discovered by an attacker. It should be known that the PostgreSQL developers have recently added a function lastval() which newly exposes such a way to bypass the check, and that this has not been officially acknowledged as a security flaw. ---(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
[HACKERS] constraints, inheritance and _RETURN ON SELECT rules
I posted about this a couple of days ago, but the post was not
complete. Trying again:
-- suppose the following table exists
CREATE TABLE many_tables (
table_id text, -- defines which virtual table encoded
att0 text,
att1 text,
att2 text,
att3 text
);
-- with some example data
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '1', 'fudge', '0.08', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '2', 'pepper', '0.44', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('dog', '1', 'morgan', 'Golden Retriever', '7');
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('bird', '1', 'boo', 'a grumpy parrot', 'Grey');
-- Goal:
-- 1) Allow incremental application migration to a relational schema
design.
-- 2) Improve performance, even for existing applications.
--
-- Method:
-- 1) Migrate the data out of many_tables into relational tables which
have
--appropriate data-types, constraints etc.
-- 2) Place rules on many_tables to support DML.
-- 3) Use inheritance + constraint to create separate child tables.
-- 4) Define _RETURN ON SELECT rule to viewify each child table with
data
--from the appropriate relational table.
CREATE TABLE cat (
cat_id INTEGER PRIMARY KEY -- was att0
, cat_name TEXT NOT NULL -- was att1
, aloofness NUMERIC(4,3) -- was att2
DEFAULT 1.0
CHECK (0.0 = aloofness AND aloofness = 1.0)
);
BEGIN;
INSERT INTO cat (cat_id, cat_name, aloofness)
SELECT CAST(att0 AS integer), att1, CAST(att2 AS numeric(4,3))
FROM many_tables WHERE table_id = 'cat';
DELETE FROM many_tables WHERE table_id = 'cat';
CREATE OR REPLACE RULE many_tables_cat_insert AS
ON INSERT TO many_tables
WHERE NEW.table_id = 'cat'
DO INSTEAD
INSERT INTO cat (cat_id, cat_name, aloofness)
VALUES ( CAST(NEW.att0 AS integer)
, NEW.att1
, CAST(NEW.att2 AS numeric(1,3))
-- gleefully ignore the other attributes
);
CREATE OR REPLACE RULE many_tables_cat_update AS
ON UPDATE TO many_tables
WHERE OLD.table_id = 'cat' AND NEW.table_id = 'cat'
DO INSTEAD
UPDATE cat
SET cat_id = CAST(NEW.att0 AS integer)
, cat_name = NEW.att1
, aloofness = CAST(NEW.att2 AS numeric(1,3))
WHERE cat_id = CAST(OLD.att0 AS integer);
CREATE OR REPLACE RULE many_tables_cat_delete AS
ON DELETE TO many_tables
WHERE OLD.table_id = 'cat'
DO INSTEAD
DELETE FROM cat
WHERE cat_id = CAST(OLD.att0 AS integer);
CREATE TABLE many_tables_cat (CHECK (table_id = 'cat')) INHERITS
(many_tables);
CREATE OR REPLACE RULE _RETURN AS
ON SELECT TO many_tables_cat DO INSTEAD
SELECT CAST('cat' AS text) AS table_id
, CAST(cat_id AS text)AS att0
, cat_nameAS att1
, CAST(aloofness AS text) AS att2
, CAST(null AS text) AS att3
FROM cat;
COMMIT;
-- test
SELECT * FROM cat; --ok
SELECT * FROM many_tables; -- oops!
ahammond=# CREATE TABLE many_tables_cat (CHECK (table_id = 'cat'))
INHERITS (many_tables);
CREATE TABLE
ahammond=# \d many_tables_cat
Table public.many_tables_cat
Column | Type | Modifiers
--+--+---
table_id | text |
att0 | text |
att1 | text |
att2 | text |
att3 | text |
Check constraints:
many_tables_cat_table_id_check CHECK (table_id = 'cat'::text)
Inherits: many_tables
ahammond=# CREATE OR REPLACE RULE _RETURN AS
ahammond-# ON SELECT TO many_tables_cat DO INSTEAD
ahammond-# SELECT CAST('cat' AS text) AS table_id
ahammond-# , CAST(cat_id AS text)AS att0
ahammond-# , cat_nameAS att1
ahammond-# , CAST(aloofness AS text) AS att2
ahammond-# , CAST(null AS text) AS att3
ahammond-# FROM cat;
CREATE RULE
ahammond=# \d many_tables_cat
View public.many_tables_cat
Column | Type | Modifiers
--+--+---
table_id | text |
att0 | text |
att1 | text |
att2 | text |
att3 | text |
View definition:
SELECT 'cat'::text AS table_id, cat.cat_id::text AS att0, cat.cat_name
AS att1, cat.aloofness::text AS att2, NULL::text AS att3
FROM cat;
ahammond=# SELECT * FROM many_tables;
ERROR: could not open relation 1663/16385/209728: No such file or
directory
Ideally, I think this should work as expected. I don't know for sure
how the constraint should fit into things, but I suspect that it should
remain and become more of an assertion. You're kind of stuck trusting
the DBA if the constraint refers to a column which doesn't even exist
in the source of the view.
Either that, or viewifying inherited tables should fail. Which is
probably the correct behaviour if the above can't reasonably be
supported.
Drew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_regress breaks on msys
Andrew Dunstan [EMAIL PROTECTED] writes: I think we'll need to have the makefile tell us what it thinks the cwd is, so if it's a virtual path we'll be able to use that. I don't see where cwd enters into it. The thing I don't understand is that the value of the make variable $(bindir) is apparently changing. How can it, when it's been hard-wired into Makefile.global by configure? regards, tom lane ---(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
Re: [HACKERS] contrib/hstore - missing, deleted or not material for
On Tue, 18 Jul 2006, Nicolai Petri wrote: If looking at http://www.sai.msu.su/~megera/oddmuse/index.cgi?Hstore the following is displayed : - License Stable version, included into PostgreSQL distribution, released under BSD license. Development version, available from this site, released under the GNU General Public License, version 2 (June 1991). - But I never found it in my contrib dir - is it just me that overlooked it and have it been removed or has it never been included ? I would really like it to be included for ease of installation on linux systems - if pgxs will fix this so it can be compiled as a standalone extension then it is a solution I can live with. USE_PGXS=1 make should works with Makefile below - override CPPFLAGS := -I. $(CPPFLAGS) MODULE_big = hstore OBJS = hstore_io.o hstore_op.o hstore_gist.o crc32.o DATA_built = hstore.sql DOCS = README.hstore REGRESS = hstore ifdef USE_PGXS PGXS := $(shell pg_config --pgxs) include $(PGXS) else subdir = contrib/hstore top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif Best regards, Nicolai Petri ---(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 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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
Re: [PATCHES] [HACKERS] 8.2 features?
Joe Conway wrote: Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Strange. Last time I checked I thought MySQL dump used 'multivalue lists in inserts' for dumps, for the same reason that we use COPY I think Andrew identified the critical point upthread: they don't try to put an unlimited number of rows into one INSERT, only a megabyte or so's worth. Typical klugy-but-effective mysql design approach ... OK, so given that we don't need to be able to do 1 million multi-targetlist insert statements, here is rev 2 of the patch. I did some testing today against mysql and found that it will easily absorb insert statements with 1 million targetlists provided you set max_allowed_packet high enough for the server. It peaked out at about 600MB, compared to my test similar last night where it was using about 3.8 GB when I killed it. So the question is, do we care? If we do, I'll start looking for a new rev 3 strategy (ideas/pointers etc very welcome). If not, I'll start working on docs and regression test. Thanks, Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_regress breaks on msys
I wrote: I don't see where cwd enters into it. The thing I don't understand is that the value of the make variable $(bindir) is apparently changing. How can it, when it's been hard-wired into Makefile.global by configure? After some googling I gather that msys' make has been hacked to transform paths between actual Windows paths and virtual paths at what-they-think-are-strategic spots. If this is correct, then I think our problem is that the method I used to inject the values of $(bindir) and friends into pg_regress.c ends up supplying actual Windows paths, where we would much rather it supplied virtual paths. An alternative method I had considered using was to have pg_regress.c get the paths by #including pg_config_paths.h. Can anyone say whether pg_config_paths.h receives real or virtual paths when building under mingw? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgxs problem
Gregory Stark wrote: I've tracked down my problem with pgxs to Makefile.global in lib/pgxs/src. These lines seem to be the culprits: bindir := $(shell pg_config --bindir) Yes, that's pretty small-minded. It should be something like PG_CONFIG = pg_config bindir := $(shell $(PG_CONFIG) --bindir) That way you can override it. I think it should be running $(pkglibdir)/bin/pg_config Actually pg_config is defined to live in $(bindir), so that would be wrong. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgxs problem
On Wed, Jul 19, 2006 at 10:29:14AM -0400, Tom Lane wrote: The documented behavior is that pgxs invokes whatever pg_config is in your PATH. How do people with multiple PostgreSQL installations keep track of which installation they're using? I use shell scripts that set PATH and a few other environment variables and then exec the command I want to run (shell aliases would also work). For example, I'd type pg73 psql to run the 7.3 version of psql (which would connect to a 7.3 server) and I'd type pg82 gmake to build an extension for 8.2devel. Prefixing each command with pgXX is a minor nuisance but by being explicit I always know what version I'm using. What are others doing? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] How does the planner deal with multiple possible indexes?
Client has a table with 9 indexes; one is on just ident_id and takes up 75181 pages, the other is on ident_id and another field and uses 117461 pages. bdata__ident_filed_departure btree (ident_id, filed_departuretime), tablespace array4 bdata_ident btree (ident_id), tablespace array4 Whats interesting is that even a simple SELECT * FROM table WHERE ident_id=1234 uses bdata__ident_filled_departure, even though it would require less IO to use bdata_ident. \d does list bdata__ident_filed_departure before bdata_ident; I'm wondering if the planner is finding the first index with ident_id in it and stopping there? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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: [HACKERS] pgxs problem
Michael Fuhr [EMAIL PROTECTED] writes:
How do people with multiple PostgreSQL installations keep track of
which installation they're using? I use shell scripts that set
PATH and a few other environment variables and then exec the command
I want to run (shell aliases would also work).
Yeah, I do something similar. In my case I generally want to switch my
attention to different installations at different times, so what I do
is make shellscripts that adjust PATH and other variables. Then I type
eg . setv81 to switch into the environment for my REL8_1_STABLE tree.
(Need the . because just executing the script normally would fail to
affect the parent shell's variables.) The script itself looks like
# Source this, eg with . bin/setvariables, to prepare for Postgres work.
STDPATH=${STDPATH:-$PATH}
STDMANPATH=${STDMANPATH:-$MANPATH}
PGSRCROOT=$HOME/REL8_1/pgsql
PGBLDROOT=$PGSRCROOT
PGINSTROOT=$HOME/version81
PATH=$PGINSTROOT/bin:$STDPATH
DEFPORT=5481
MANPATH=$PGINSTROOT/man:$STDMANPATH
PGDATA=$PGINSTROOT/data
PMOPTIONS=-p 5481 -i -F
PMLOGFILE=server81.log
export PGSRCROOT PGBLDROOT PGINSTROOT PATH MANPATH STDPATH STDMANPATH
export DEFPORT PGDATA PMOPTIONS PMLOGFILE
The reason for the passel of variables is that I have some other scripts
that use the variables to do the right thing in all installations.
For instance the script that invokes configure includes
--with-pgport=$DEFPORT --prefix=$PGINSTROOT
In particular the point of STDPATH/STDMANPATH is to capture the shell's
original path settings so that switching between installations
repeatedly doesn't cause PATH and MANPATH to grow indefinitely.
You can probably guess what all the other vars are for.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] pgxs problem
Peter Eisentraut [EMAIL PROTECTED] writes: Yes, that's pretty small-minded. It should be something like PG_CONFIG = pg_config bindir := $(shell $(PG_CONFIG) --bindir) That way you can override it. No objection here, although I'm not entirely convinced why anyone would prefer doing that to setting their PATH. If the pg_config you want isn't (first in) your PATH, none of the other PG programs will be either, which seems like an awkward situation for getting any PG-related work done. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Max size of a btree index entry
On Tue, Jul 11, 2006 at 10:02:49AM -0400, Tom Lane wrote: Currently, we restrict btree index tuples to a size that ensures three of them will fit on a page. The motivation for this is the following two considerations: 1. In a non-rightmost page, we need to include a high key, or page boundary key, that isn't one of the useful data keys. Why does a leaf page need a boundary key? ISTM if that wasn't the case, we could actually allow keys to be nearly 8K, constrained by a non-leaf page needing to include two pointers. I guess I must be missing something here (and nbtree/README isn't helping). 2. In a non-leaf page, there had better be at least two child pages (downlink entries), else we have failed to subdivide the page's key range at all, and thus there would be a nonterminating recursion. However: a non-leaf page actually has one more pointer than key, eg a page with three children needs only two data keys: entire key range assigned to page -- -- range 1 -- boundary key -- range 2 -- boundary key -- range 3 -- | | | v v v child page 1 child page 2 child page 3 We implement this by having the first data tuple on a non-leaf page contain only a downlink TID and no key data, ie it's just the header. So it appears to me that we could allow the maximum size of a btree entry to be just less than half a page, rather than just less than a third of a page --- the worst-case requirement for a non-leaf page is not three real tuples, but one tuple header and two real tuples. On a leaf page we might manage to fit only one real data item, but AFAICS that doesn't pose any correctness problems. Obviously a tree containing many such pages would be awfully inefficient to search, but I think a more common case is that there are a few wide entries in an index of mostly short entries, and so pushing the hard limit up a little would add some flexibility with little performance cost in real-world cases. Have I missed something? Is this worth changing? regards, tom lane ---(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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Max size of a btree index entry
Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, Jul 11, 2006 at 10:02:49AM -0400, Tom Lane wrote: 1. In a non-rightmost page, we need to include a high key, or page boundary key, that isn't one of the useful data keys. Why does a leaf page need a boundary key? So you can tell whether a proposed insertion ought to go into this page, or the one to its right. The tree descent logic doesn't guarantee that you descend to exactly the correct page --- if concurrent page splits are going on, you might have to move right one or more times after reaching the leaf level. You need the boundary key to make this test correctly. And of course, the reason there's no high key on the rightmost page is exactly that it has no right-hand neighbor, hence no upper limit on its delegated key space. regards, tom lane ---(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: [HACKERS] How does the planner deal with multiple possible indexes?
On 7/19/06, Jim C. Nasby [EMAIL PROTECTED] wrote: [snip] \d does list bdata__ident_filed_departure before bdata_ident; I'm wondering if the planner is finding the first index with ident_id in it and stopping there? From my own experience it was grabbing the first that has the requested field as its first member.. I haven't looked at the code to see if that is the intended behavior. ---(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
Re: [HACKERS] plPHP and plRuby
Tom Lane wrote: The difference is that I will have reasonable confidence that the README.TXT under src/pl will give instructions that match the version of PostgreSQL that I have. I assume that README will call out the version of PL/R or PL/Ruby that I want that was tested with the release of PostgreSQL I have. On what do you base that assumption? A README file laying about in an otherwise unused part of the source tree is the very definition of out of sight, out of mind. I was hoping it would say something like PostgreSQL 8.2.0 has been tested with version XX.YY.01 of PL/Whatever You can install it by getting that release and doing the following. with specific version numbers rather than links to URLS that would change. It that wasn't the intent of the README.TXT, I'm not sure what is. I can pretty much guarantee you that it will NOT get updated, especially not during minor releases. Even if it is up to date at the instant we put out a release, it'll be obsolete as soon as the external project makes an update release. ISTM links like this are far better kept on project websites ... I was hoping that this README.TXT point to the specific old version that was tested in much the same way that the old 8.0.0 source tree continues to have the same PL/pgsql that has always been there. If the external project updates their release and breaks compatability I think they should be encouraged to update the README.TXT to say something like PostgreSQL 8.2.1 has been tested with PL/Whatever version XX.YY.99 If they don't make that update, the README would PostgreSQL 8.2.0 has been tested with PL/Whatever version XX.YY.00 I would imagine with this README.TXT proposal, it's the responsibility of the PL/XXX developer to port their PL to PostgreSQL during the Beta, and if the did and tested it, the release will point to the version of the PL supported by the PL maintainer for that version. And if they didn't? I was just noticing that the current release of plruby contains installation instructions that appear to date to 7.3. If he can't be bothered to update his own docs, what are the odds that he'll submit timely updates for a README in the main source tree? Yeah. Good point. I guess the alternatives are that the README still say PostgreSQL 7.3.0 has been tested with PL/Ruby version X.Y.Z or We are unaware of up-to-date instructions for PL/Ruby. Good Luck. Though if you'd welcome people in the community to submit patches to that README I suspect they'll be updated even more regularly than 2002 or whenever 7.3 come out. If I spent time figuring it out, I wouldn't mind submitting a patch for such a README; and I suspect the other guys who blog about PL/Ruby installation instructions in late 2005 would be happy to submit such patches as well. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plPHP and plRuby
Ron Mayer wrote: Tom Lane wrote: The difference is that I will have reasonable confidence that the README.TXT under src/pl will give instructions that match the version of PostgreSQL that I have. I assume that README will call out the version of PL/R or PL/Ruby that I want that was tested with the release of PostgreSQL I have. On what do you base that assumption? A README file laying about in an otherwise unused part of the source tree is the very definition of out of sight, out of mind. I was hoping it would say something like PostgreSQL 8.2.0 has been tested with version XX.YY.01 of PL/Whatever You can install it by getting that release and doing the following. with specific version numbers rather than links to URLS that would change. It that wasn't the intent of the README.TXT, I'm not sure what is. This is way too DIY. The only thing I think might be worthwhile (and it would help from a buildfarm POV) would be something to assist an integrated build from disparate sources. Just a Readme doesn't come close to what I think we need in the general case. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgxs problem
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Yes, that's pretty small-minded. It should be something like PG_CONFIG = pg_config bindir := $(shell $(PG_CONFIG) --bindir) That way you can override it. No objection here, although I'm not entirely convinced why anyone would prefer doing that to setting their PATH. If the pg_config you want isn't (first in) your PATH, none of the other PG programs will be either, which seems like an awkward situation for getting any PG-related work done. Well, with the above change, both camps would be happy. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How does the planner deal with multiple possible indexes?
Gregory Maxwell [EMAIL PROTECTED] writes: On 7/19/06, Jim C. Nasby [EMAIL PROTECTED] wrote: [snip] \d does list bdata__ident_filed_departure before bdata_ident; I'm wondering if the planner is finding the first index with ident_id in it and stopping there? From my own experience it was grabbing the first that has the requested field as its first member.. I haven't looked at the code to see if that is the intended behavior. Ordering would only matter if the estimated costs were exactly the same, which they probably shouldn't be for indexes with such different sizes. However, if the estimated number of matching rows were real small, you might be winding up with a one page to fetch estimate in either case. Jim didn't provide enough details to guess what the cost estimates actually are... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Freezing tuples on pages dirtied by vacuum
Currently, the loop in vacuumlazy.c that scans through the tuples on a page checks each tuple to see if it needs to be frozen (is it's Xmin older than half-way to wrap-around). ISTM that as soon as vacuum dirties a page, it might as well update all tuples it can (any where Xmin GetOldestXmin()), since that won't take much time compared to the cost of writing the page out. This would help prevent the need to dirty the page in the distant future for no reason other than to freeze tuples. Granted, the old code/checks would still have to stay in place to ensure that tuples were vacuumed before they got too old, but that's not much overhead compared to writing the page to disk. Comments? If people think this is a good idea I should be able to come up with a patch. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Max size of a btree index entry
On Wed, Jul 19, 2006 at 06:23:44PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, Jul 11, 2006 at 10:02:49AM -0400, Tom Lane wrote: 1. In a non-rightmost page, we need to include a high key, or page boundary key, that isn't one of the useful data keys. Why does a leaf page need a boundary key? So you can tell whether a proposed insertion ought to go into this page, or the one to its right. The tree descent logic doesn't guarantee that you descend to exactly the correct page --- if concurrent page splits are going on, you might have to move right one or more times after reaching the leaf level. You need the boundary key to make this test correctly. And of course, the reason there's no high key on the rightmost page is exactly that it has no right-hand neighbor, hence no upper limit on its delegated key space. Could you not just scan right and see what the first key was? Thought granted, that means there's a chance of a wasted page scan, but I think that'd be somewhat of a corner case, so it might not be bad. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] How does the planner deal with multiple possible indexes?
On Wed, Jul 19, 2006 at 07:00:40PM -0400, Tom Lane wrote: Gregory Maxwell [EMAIL PROTECTED] writes: On 7/19/06, Jim C. Nasby [EMAIL PROTECTED] wrote: [snip] \d does list bdata__ident_filed_departure before bdata_ident; I'm wondering if the planner is finding the first index with ident_id in it and stopping there? From my own experience it was grabbing the first that has the requested field as its first member.. I haven't looked at the code to see if that is the intended behavior. Ordering would only matter if the estimated costs were exactly the same, which they probably shouldn't be for indexes with such different sizes. However, if the estimated number of matching rows were real small, you might be winding up with a one page to fetch estimate in either case. Jim didn't provide enough details to guess what the cost estimates actually are... Indeed, if I find a case where there's a large enough number of rows it will choose the smaller index. But I'm wondering if it would be better to always favor the smaller index, since it would (presumably) be easier to keep it in cache? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Max size of a btree index entry
Jim C. Nasby [EMAIL PROTECTED] writes: Could you not just scan right and see what the first key was? Thought granted, that means there's a chance of a wasted page scan, but I think that'd be somewhat of a corner case, so it might not be bad. No, because (a) that confuses the first key that happens to be on a page with its keyspace boundary --- what happens when you need to delete that data key? and (b) because of locking considerations, you don't want to move right and then have to back up. You'd have to hold lock on the first page while reading in the second, which makes for a nontrivial performance hit. regards, tom lane ---(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: [HACKERS] Freezing tuples on pages dirtied by vacuum
Jim C. Nasby [EMAIL PROTECTED] writes: ISTM that as soon as vacuum dirties a page, it might as well update all tuples it can (any where Xmin GetOldestXmin()), since that won't take much time compared to the cost of writing the page out. Perhaps not, but what it will do is destroy data that you might wish you had later. Check the archives and note how often we ask people for xmin values when trying to debug a problem. I don't think it's a good idea for aggressive freezing of tuples to be the default behavior. Moreover, I can't see that there'd be any real gain from having done it --- it doesn't look to me like it would save any vacuum-to-prevent-wraparound operations, since nothing would happen at non-dirty pages. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] How does the planner deal with multiple possible indexes?
Jim C. Nasby [EMAIL PROTECTED] writes: Indeed, if I find a case where there's a large enough number of rows it will choose the smaller index. But I'm wondering if it would be better to always favor the smaller index, since it would (presumably) be easier to keep it in cache? AFAICS, in existing releases that should happen, because the cost estimate varies with the size of the index. And it does happen for me in simple tests. You did not provide the requested information to help us find out why it's not happening for you. (I'm a bit worried about whether CVS HEAD may have broken this behavior with the recent changes in the indexscan cost equations ... but unless you are working with HEAD that's not relevant.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Resurrecting per-page cleaner for btree
On Wed, Jul 12, 2006 at 03:59:01PM +0900, ITAGAKI Takahiro wrote: Hi Hackers, Can we resurrect the patch proposed by Junji TERAMOTO? It removes unnecessary items before btree pages split. http://archives.postgresql.org/pgsql-patches/2006-01/msg00301.php There was a problem in the patch when we restarted scans from deleted tuples. But now we scan pages at-a-time, so the problem is resolved, isn't it? http://archives.postgresql.org/pgsql-patches/2006-05/msg8.php I think this feature is independent from the SITC project and useful for heavily-updated indexes. If it is worthwhile, I'll revise the patch to catch up on HEAD. Tom's comment about the patch needing better comments still holds. If nothing else, do the best you can with the comments in English and someone else can clean the grammar up. It's also not clear to me if Tom's comment about not deleting LP_DELETE tuples at-will is still valid or not. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Online index builds
On Sat, Jul 15, 2006 at 09:10:46PM -0400, Greg Stark wrote: Hannu Krosing [EMAIL PROTECTED] writes: Maybe we can show progress indicators in status line (either pg_stat_activity.current_query or commandline shown in ps), like WAITING TO START PHASE 1 - WAITING FOR TRANSACTION XXX TO COMPLETE or INSERTING INDEX ENTRY N OF M changing every few seconds. Hm. That would be very interesting. I'll say that one of the things that impressed me very much with Postgres moving from Oracle was the focus on usability. Progress indicators would be excellent for a lot of operations. That said I'm not sure how much I can do here. For a substantial index we should expect most of the time will be spent in the tuplesort. It's hard to see how to get any sort of progress indicator out of there and as long as we can't it's hard to see the point of getting one during the heap scan or any of the other i/o operations. I'd love to have any kind of progress indication for any sorts that spill to disk, and there's any number of other long-running operations where progress info would be very welcome. I certainly wouldn't let lack of a progress indicator for sorts prevent you from adding one. I like the idea of periodically updating both current_query and the commandline that ps shows. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] always denying corruption
Marc Munro wrote: For the record, here are the results of our (ongoing) inevstigation into the index/heap corruption problems I reported a couple of weeks ago. We were able to trigger the problem with kernels 2.6.16, 2.6.17 and 2.6.18.rc1, with 2.6.16 seeming to be the most flaky. By replacing the NFS-mounted netapp with a fibre-channel SAN, we have eliminated the problem on all kernels. From this, it would seem to be an NFS bug introduced post 2.6.14, though we cannot rule out a postgres bug exposed by unusual timing issues. Our starting systems are: Sun v40z 4 x Dual Core AMD Opteron(tm) Processor 875 Kernel 2.6.16.14 #8 SMP x86_64 x86_64 x86_64 GNU/Linux (and others) kernel boot option: elevator=deadline 16 Gigs of RAM postgresql-8.0.8-1PGDG Bonded e1000/tg3 NICs with 8192 MTU. Slony 1.1.5 NetApp FAS270 OnTap 7.0.3 Mounted with the NFS options rw,nfsvers=3,hard,rsize=32768,wsize=32768,timeo=600,tcp,noac Jumbo frames 8192 MTU. All postgres data and logs are stored on the netapp. All tests results were reproduced with postgres 8.0.8 __ Marc On Fri, 2006-06-30 at 23:20 -0400, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: We tried all of these suggestions and still get the problem. Nothing interesting in the log file so I guess the Asserts did not fire. Not surprising, it was a long shot that any of those things were really broken. But worth testing. We are going to try experimenting with different kernels now. Unless anyone has any other suggestions. Right at the moment I have no better ideas :-( regards, tom lane On a good stock day, some levity is justified. How are hackers like politicians? -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.1/390 - Release Date: 7/17/2006 ---(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: [HACKERS] pg_regress breaks on msys
Tom Lane wrote: I wrote: I don't see where cwd enters into it. The thing I don't understand is that the value of the make variable $(bindir) is apparently changing. How can it, when it's been hard-wired into Makefile.global by configure? After some googling I gather that msys' make has been hacked to transform paths between actual Windows paths and virtual paths at what-they-think-are-strategic spots. If this is correct, then I think our problem is that the method I used to inject the values of $(bindir) and friends into pg_regress.c ends up supplying actual Windows paths, where we would much rather it supplied virtual paths. Unless it also lies on the echoed command line this seems an unconvincing explanation. The seahorse log says: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I../../../src/include -I./src/include/port/win32 -DEXEC_BACKEND -I/c/tcl/include -I../../../src/include/port/win32 '-DPGBINDIR=/home/pgbuild/pgfarmbuild/HEAD/inst/bin' '-DLIBDIR=/home/pgbuild/pgfarmbuild/HEAD/inst/lib' '-DPGSHAREDIR=/home/pgbuild/pgfarmbuild/HEAD/inst/share/postgresql' '-DHOST_TUPLE=i686-pc-mingw32' '-DMAKEPROG=make' '-DSHELLPROG=/bin/sh.exe' -c -o pg_regress.o pg_regress.c If those -D values are not what it gets then that would be quite evil. We used to pass these values almost same way when we first did initdb in C, and I don't recall any such problems. We had: override CPPFLAGS := -DPGBINDIR=\$(*bindir*)\ -DPGDATADIR=\$(*datadir*)\ -DFRONTEND -I$(*libpq_srcdir*) $(*CPPFLAGS*) There is also this warning, by the way: pg_regress.c:63: warning: 'shellprog' defined but not used cheers andrew ---(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
