Re: [HACKERS] Scalable postgresql using sys_epoll
Anthony_Barker wrote: IBM has rewritten their Domino database system to use the new sys_epoll call available in the Linux 2.6 kernel. Would Postgresql benefit from using this API? Is anyone looking at this? Anthony http://xminc.com/mt/ I'm not familiar enough with the postgres internals, but is using libevent (http://monkey.org/~provos/libevent/) an option? It uses state triggered, rather than edge triggered, interface, and it automatically selects the best API for the job (epoll, poll, select). I'm not sure whether it's available for all the platforms postgres is available for. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] log_line_info
On Tue, 09 Mar 2004 10:02:14 -0500, Andrew Dunstan [EMAIL PROTECTED] wrote: After this is applied (fingers crossed) and everyone is happy, I will submit a patch to remove log_timestamp, log_pid and (if we are agreed on it) log_source_port. Is there agreement on removing these 3 config vars? Please don't. Declare them obsolete for 7.5 and remove them in a later release. Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PITR Functional Design v2 for 7.5
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: When I'm doing a file level hot backup, I can't be sure about the backup order. To be sure the cluster is in a consistent state regarding checkpoints, pg_clog must be the first directory backed up. You are going off in the wrong direction entirely. Any hot-backup design that thinks safety can be ensured by back up file A before file B considerations is wrong. That's because Postgres doesn't necessarily dump dirty blocks into the data area (or clog area) at any particular time. Therefore, a filesystem-level backup taken while the postmaster is running is basically certain to be inconsistent. You can *not* avoid this by being careful about the order you dump files in. Heck, you can't even be certain that a file you dump is internally consistent. Maybe my wording was misleading, seems Simon understood me as int was meant. With consistent state regarding checkpoints I meant that all transactions that are marked as committed with the checkpoint are really present in the data files. Of course, there might be even more transactions which haven't been checkpointed so far, they'll need WAL replay. To clarify: I'd expect a cluster to be workable, if I - disable VACUUM until backup completed - issue CHECKPOINT - backup clog (CHECKPOINT and backup clog are the backup checkpoint) - backup all datafiles (which include at least all completed transaction data at checkpoint time) and then - restore datafiles and clog - bring up pgsql. Certainly, all transactions after the backup checkpoint are lost. There might be fragments of newer transactions in data files, but they were never committed according to clog and thus rolled back. WAL replay would add more completed transactions, making the cluster more up-to-date, but omitting this would be sufficient in many desaster recovery scenarios. Did I miss something? If so, not only an API to get WAL data ordered out of pgsql is needed, but for the whole cluster. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] grants
Edgar Mares wrote: hi there i'm having troubles to find how to GRANT SELECT ON all-tables-onmydb TO specificuser this is just to give the access to specificuser to query the database and find troubles on it pgAdmin II has a tool for that (Security wizard; pgAdmin III has it on the todo-list) Regards, Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] log_line_info
Please don't. Declare them obsolete for 7.5 and remove them in a later release. Nah, just remove them. We've removed, added and changed so many config options and no-one's ever complained... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] question about char/wchar/mb utils
Hello hackers, I'm working on a very small patch to add syntax error localisation on the client side in psql, as it seems to be the place. Something like: ERROR: syntax error at character 12345 QUERY: ... WHERE foo IS NUL AND ... QUERY: ^ My current issue is how to build the query excerpt to be displayed. I'm in the psql/common.c file. The query is a char*, and I guess it is in the client encoding, so that one character is not one byte. My first idea was to transform the char into a wchar and then work directly and simply on the wchar string (to fix tabs and so) ... However, although I have found a function to build the wchar string (pg_mb2wchar), I haven't found the reverse (yet) :-(, that is to convert my wchar extract to a char string compatible with the client encoding. Is there such a function somewhere? It seems that wchar is a one-way type, once you're there you're stuck with it:-( If not, what are the other options? The issue is to be able to work on a constant width string without having to care about different mb encodings, otherwise the code is going to be a little bit tricky... I guess I can do it, but the reverse conversion function looked more appealing to me;-) Or am still I off my shoes? (translator: a cote de mes pompes;-); Thanks, for any advice on this point or others, have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Scalable postgresql using sys_epoll
On Wed, 10 Mar 2004, Shachar Shemesh wrote: IBM has rewritten their Domino database system to use the new sys_epoll call available in the Linux 2.6 kernel. Would Postgresql benefit from using this API? Is anyone looking at this? I'm not familiar enough with the postgres internals, but is using libevent (http://monkey.org/~provos/libevent/) an option? It uses state triggered, rather than edge triggered, interface, and it automatically selects the best API for the job (epoll, poll, select). I'm not sure whether it's available for all the platforms postgres is available for. libevent is cool, but postgres uses a process-per-client model, so the number of file descriptors of active interest to a backend at any given time is low. Matthew. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Timing of 'SELECT 1'
Bruce Momjian wrote: I am timing small queries, and found that a PREPARE/EXECUTE of SELECT 1 takes about 1.2ms on my machine. A normal SELECT doesn't take much longer, so I am wondering why a simpler query isn't faster. Looking at log_executor_stats, I see the following. Execute shows nothing taking much time, mostly .2ms, but the total seems high. I wonder if one of our standard query start/stop functions is taking too long and can be optimized. There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case conditions which limits the amount of queries you can fire off in 1 second, no matter how simple. In certain rare cases this is something of a bottleneck. In my personal case it would be nice to see that time lower because converted COBOL applications tend to generate a lot of 'simple' queries. Merlin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Functional Design v2 for 7.5
Andreas Pflug [EMAIL PROTECTED] writes: To clarify: I'd expect a cluster to be workable, if I - disable VACUUM until backup completed - issue CHECKPOINT - backup clog (CHECKPOINT and backup clog are the backup checkpoint) - backup all datafiles (which include at least all completed transaction data at checkpoint time) and then - restore datafiles and clog - bring up pgsql. Why is that a useful approach? You might as well shut down the postmaster and do a cold filesystem backup, because you are depending on the data files (including clog) not to change after the checkpoint. You cannot make such an assumption in a running database. Preventing VACUUM does not help btw. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] question about char/wchar/mb utils
If not, what are the other options? I don't think you have any: you have to use PQmblen. Depending on wchar facilities would be unportable even if they did everything you wanted. I meant pg_wchar. It's in the postgres source, should be pretty portable. It might be convenient to build an integer array of character start offsets and then work with that as if it were a wchar array. Yep, I thought about this kind of thing. Thanks for your advice, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] grants
On Wed, 10 Mar 2004, Andreas Pflug wrote: Edgar Mares wrote: hi there i'm having troubles to find how to GRANT SELECT ON all-tables-onmydb TO specificuser this is just to give the access to specificuser to query the database and find troubles on it pgAdmin II has a tool for that (Security wizard; pgAdmin III has it on the todo-list) The problem that cannot be solved with either this or a function that loops and grants on each table is that it is not a permanent grant of what the admin had in mind. If a new table is added or an existing table is dropped and recreated, the grants must be done again. The real use of a SELECT ANY TABLE permission is ignorance of schema updates. Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PITR Functional Design v2 for 7.5
Josh Berkus [mailto:[EMAIL PROTECTED] wal_archive_policy and enable/disable archiving accordingly. This parameter can only be changed at server start. (This is required because the initial step of archiving each xlog is performed by the backend; if this were changeable after boot, then it might be possible for an individual backend to override the wal_archive_policy and choose not to archive - which would then effect the whole system and all users, not just the user making that choice). It is considered less desirable to Let me voice a real-world exception to this policy. Imagine that you are running an OLAP or decision-support database that analyzes data coming from an external source. Once a day you load 250MB of data via COPY and then does transformations on that data. While doing the load, you do *not* want the archiver running, as it would quickly fill up the WAL partition and backlog the archive tape. Under the proposed PITR spec, the only way to handle this would be to: 1) Full back up 2) Shut down PG 3) Restart PG without archiving 4) Load the data 5) Shut down PG again 6) Restart PG with archiving 7) Full back-up again. DBAs would like it much more if starting/stopping the archiver was possible via a superuser (not regular user) GUC.This would allow a much faster cycle: 1) Full back up 2) Stop archiving 3) Load the data 4) Restart archiving 5) Full back-up The scenario you mention is what I'd like to do, but don't just yet see how. I'd welcome input on this point, since I don't fully understand GUCs: Thinking about this: 1. Since the backends run XLogArchiveNotify(), they must all do so identically. One slip invalidates all the work of all the others. GUC Options are: INTERNAL - not appropriate POSTMASTER - what I originally envisaged, but not what you want SIGHUP - seems to allow different parameter settings in each backend BACKEND - not appropriate SUSET - maybe what you're looking for??? USERLIMIT - no USERSET - absolutely no 2. Maybe have Postmaster run something every so often that looks for full xlogs and then executes XLogArchiveNotify() for them? Thoughts anyone? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] log_line_info
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Please don't. Declare them obsolete for 7.5 and remove them in a later release. Nah, just remove them. We've removed, added and changed so many config options and no-one's ever complained... I agree with Chris; this would be taking compatibility concerns a bit far. None of these variables are likely to be touched except through postgresql.conf (I don't think we even allow them to be SET interactively). And you can never simply take your old .conf file and plop it down into a new release. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PITR Functional Design v2 for 7.5
From: Josh Berkus [mailto:[EMAIL PROTECTED] SIGHUP - seems to allow different parameter settings in each backend Nope. SIGHUP means that you need to send a HUP to the postmaster, such as you would with changes to pg_hba.conf. SUSET - maybe what you're looking for??? Yes. This means that it can be changed, at runtime, but by the Superuser only. This is used for several settings which are possible to change at runtime but take effect system-wide. SUSET it is then. I'll update the design doc Thanks, Simon ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] question about char/wchar/mb utils
Fabien COELHO [EMAIL PROTECTED] writes: If not, what are the other options? I don't think you have any: you have to use PQmblen. Depending on wchar facilities would be unportable even if they did everything you wanted. It might be convenient to build an integer array of character start offsets and then work with that as if it were a wchar array. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] log_line_info
Tom Lane wrote: I agree with Chris; this would be taking compatibility concerns a bit far. None of these variables are likely to be touched except through postgresql.conf (I don't think we even allow them to be SET interactively). And you can never simply take your old .conf file and plop it down into a new release. Also, I selfishly want to get this out of my hair and move on :-) The docs already state that log_source_port is useless. patch forthcoming soon. cheers andrew ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] optimizing impossible matches
Tom Lane wrote: The optimizer has no knowledge of specific operators except what it finds in the system catalogs. It has no way in general to determine that a comparison involving nonconstant values must always fail. Even if we could do it, I am dubious that it would be worth expending the cycles on every query to determine whether the situation holds. AFAICS those would be wasted cycles on the huge majority of queries. Ok, fair enough. And queries that did have this issue could be easily rewritten... Still, there is something that doesn't site quite right with me...my problems is with SQL really, not Postgres. For example, the server forbids 'abcd'::char(3) but allows 'abcd' char(3) because the operator is not bound to the specific type, but to the general type and ignores type constraints. In other words, SQL implicitly allows comparison between objects of different domains if the domains differ only by constraint (including size). Anyways, thanks for taking the time to answer. Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [DEFAULT] Daily digest v1.4318 (23 messages)
Chad, I'm talking about the stuff that the other poster (cant see his name right now, sorry) doubts will ever be in postgres. ie you can seek to anywhere in the Btree using a row offset as a search key. And this is more useful than LIMIT # OFFSET # on queries, how, exactly? I'd love to hear why this would be hard to support in a materialized view. Could you explain that ? Berkeley DB supports it. Berkeley DB is not a Relational Database. It's not a question of hard to support. It's a question of don't want to support. One of the core tenets of relational database theory is that there are no row numbers; rows only have a fixed order as a part of a sorted final output set (e.g. a query with an ORDER BY). I don't know what kind of application you're trying to support that you think row numbers are such a keen idea. As far as we're concerned, row numbers are an inefficient throwback to the pre-relational databases of the early 1980's; why would we want them? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] log_line_info
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Please don't. Declare them obsolete for 7.5 and remove them in a later release. Nah, just remove them. We've removed, added and changed so many config options and no-one's ever complained... I agree with Chris; this would be taking compatibility concerns a bit far. None of these variables are likely to be touched except through postgresql.conf (I don't think we even allow them to be SET interactively). And you can never simply take your old .conf file and plop it down into a new release. And the other reason to remove them is that if you don't, you will get all sorts of confusion about people asking, Which one should I use for pid logging? Looks like both work. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Functional Design v2 for 7.5
The only way we can support file-level hot backup is in conjunction with PITR-style WAL log archiving. It is okay for the data area dump to be inconsistent, so long as your recovery process includes replay of WAL starting at some checkpoint before the filesystem dump started, and extending to some point after the filesystem dump finished. Replaying WAL will correct the inconsistencies. And the last checkpoint info resides in pg_control, and not in pg_clog, no ? So basically a PITR restore would need to adjust the pg_control file after filesystem restore and before starting recovery. Maybe it can take that info from the oldest available WAL ? The OP would only need to ensure, that only such logs that need to be rolled forward are visible (in the correct directory) to the recovery. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PITR Functional Design v2 for 7.5
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: Why is that a useful approach? You might as well shut down the postmaster and do a cold filesystem backup, We're talking about *hot* backup, aren't we? Exactly. The approach you're sketching can't work for hot backup, because it effectively assumes that the database won't be changing. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PITR Functional Design v2 for 7.5
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: Why is that a useful approach? You might as well shut down the postmaster and do a cold filesystem backup, We're talking about *hot* backup, aren't we? Exactly. The approach you're sketching can't work for hot backup, because it effectively assumes that the database won't be changing. Well in the first place my posting was to express my suspicion that WAL replay relies on clog/pg_control being accurate, i.e. transactions marked as flushed must be on disk. AFAICS this is the consequence of WAL replay implementation. In case of hot backup, this means that data files must not be older than clog. Do you agree? So PITR needs a mechanism to insure this at backup time. Next question would be: If the point in time I'd like to recover is that very backup checkpoint time, do I need xlog at all? Regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] selective statement logging
The TODO list contains this item which I said I would look at:
Allow logging of only data definition(DDL), or DDL and modification
statements
The trouble I see is that we currently do statement logging before we
have examined the query string at all, in the code shown below from
src/backend/tcop/postgres.c.
I guess I could construct one or more regexes to examine the query
string, although that might affect performance a bit (of course, I would
precompile the patterns).
Any other ideas on how to proceed?
cheers
andrew
List *
pg_parse_query(const char *query_string)
{
List *raw_parsetree_list;
if (log_statement)
ereport(LOG,
(errmsg(statement: %s, query_string)));
if (log_parser_stats)
ResetUsage();
raw_parsetree_list = raw_parser(query_string);
if (log_parser_stats)
ShowUsage(PARSER STATISTICS);
return raw_parsetree_list;
}
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] About hierarchical_query of Oracle
On Wed, 10 Mar 2004, Li Yuexin wrote: Who can tell me how to complete oracle's hierarchical_query through postgresql? Look in the contrib/tablefunc directory for the connect_by function. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Timing of 'SELECT 1'
Neil Conway wrote: Bruce Momjian [EMAIL PROTECTED] writes: I am timing small queries, and found that a PREPARE/EXECUTE of SELECT 1 takes about 1.2ms on my machine. A normal SELECT doesn't take much longer, so I am wondering why a simpler query isn't faster. log_executor_stats output isn't really too helpful -- if you can get some gprof or oprofile output, we might have a better chance of seeing if there is anything we can improve. The problem with gprof is that I am going to see all the backend startup stuff too, no? Is there a way to get a dump just the run of the query? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Timing of 'SELECT 1'
Merlin Moncure wrote: Bruce Momjian wrote: I am timing small queries, and found that a PREPARE/EXECUTE of SELECT 1 takes about 1.2ms on my machine. A normal SELECT doesn't take much longer, so I am wondering why a simpler query isn't faster. Looking at log_executor_stats, I see the following. Execute shows nothing taking much time, mostly .2ms, but the total seems high. I wonder if one of our standard query start/stop functions is taking too long and can be optimized. There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case conditions which limits the amount of queries you can fire off in 1 second, no matter how simple. In certain rare cases this is something of a bottleneck. In my personal case it would be nice to see that time lower because converted COBOL applications tend to generate a lot of 'simple' queries. Yes, most of that might be network time. I am using log_duration, which I think just tests backend time, not network transfer time, but I might be wrong. I want to look into this as it seems no one knows the answer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] grants
Kris Jurka wrote:
On Wed, 10 Mar 2004, Andreas Pflug wrote:
Edgar Mares wrote:
hi there i'm having troubles to find how to
GRANT SELECT ON all-tables-onmydb TO specificuser
this is just to give the access to specificuser to query the
database and find troubles on it
pgAdmin II has a tool for that (Security wizard; pgAdmin III has it on
the todo-list)
The problem that cannot be solved with either this or a function that
loops and grants on each table is that it is not a permanent grant of what
the admin had in mind. If a new table is added or an existing table is
dropped and recreated, the grants must be done again. The real use of a
SELECT ANY TABLE permission is ignorance of schema updates.
Hm, does this exist in other DBMS?
As soon as roles are implemented, there might be a default role
('public') for this. Until then, using groups solves most of the
problems (well, you certainly still need to GRANT rights to your
preferred group).
Regards,
Andreas
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timing of 'SELECT 1'
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I am timing small queries, and found that a PREPARE/EXECUTE of SELECT
1 takes about 1.2ms on my machine. A normal SELECT doesn't take much
longer, so I am wondering why a simpler query isn't faster.
Define normal SELECT. I can think of plenty of people who would be
overjoyed if their average SELECT was only a couple millisecs.
OK, you asked, so here it is. I define a simple select as pulling a
single column from a single table using uniquely indexed key. It takes
1.182 ms on my machine, almost the same time as SELECT 1.
This tells me that the actual table access isn't the issue, it is the
overhead of the query processing itself. What I want to find out is
where that 1ms is coming from, because it isn't coming from the
executor. What I might do is to add ResetUsage/ShowUsage calls around
the other parts of the query loop to find who is using the time.
I am using log_duration, so this isn't measuring network time, just time
in the backend (at least I think so).
Run the attached script through psql and you will see the times.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
\set ECHO all
\timing
DROP TABLE perftest;
CREATE TABLE perftest (col text);
-- prime table with one row
INSERT INTO perftest VALUES ('0.364461265208414');
-- continously double the table size
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
-- insert a constant in the middle of the table, for use later
INSERT INTO perftest VALUES ('0.608254158221304');
INSERT INTO perftest SELECT random()::text FROM perftest;
-- 32770 rows
-- vacuum, create index
VACUUM ANALYZE perftest;
CREATE INDEX i_perftest ON perftest (col);
-- reduce chance of checkpoint during tests
CHECKPOINT;
-- turn on logging
SET log_duration = TRUE;
SET client_min_messages = 'log';
-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
-- first time the entire statement
SET log_statement_stats = TRUE;
-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
-- now log each query stage
SET log_statement_stats = FALSE;
SET log_parser_stats = TRUE;
SET log_planner_stats = TRUE;
SET log_executor_stats = TRUE;
-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Functional Design v2 for 7.5
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: To clarify: I'd expect a cluster to be workable, if I - disable VACUUM until backup completed - issue CHECKPOINT - backup clog (CHECKPOINT and backup clog are the backup checkpoint) - backup all datafiles (which include at least all completed transaction data at checkpoint time) and then - restore datafiles and clog - bring up pgsql. Why is that a useful approach? You might as well shut down the postmaster and do a cold filesystem backup, We're talking about *hot* backup, aren't we? Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] selective statement logging
Andrew Dunstan wrote: The TODO list contains this item which I said I would look at: Allow logging of only data definition(DDL), or DDL and modification statements The trouble I see is that we currently do statement logging before we have examined the query string at all, in the code shown below from src/backend/tcop/postgres.c. I guess I could construct one or more regexes to examine the query string, although that might affect performance a bit (of course, I would precompile the patterns). Any other ideas on how to proceed? Yes, look at how the command tag is grabbed for the PS display, and do the log checks at that point. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Default Stats Revisited
Folks, Early on in the default_stats thread, I made a proposal that got dropped without discussion. I'd like to revisit it, because I still think it's a good idea. The Issue: The low default_stats_target of 10 is not sufficient for many complex queries involving multi-column correlation or oddly distributed data. Yet modestly increasing the stats target for *all* columns, as demonstrated, substantially increases the time required for Analyze, without gain on most queries. If only there were a way to automatically increas the default stats on only important columns, and not on other columns! Yet if we burden the DBA with flagging important colummns all over the database, we haven't saved him/her any work. Ah, but there is a way! Most important columns are already indicated ... because they are indexed.If we implemented a system where indexed columns would have a significantly higher stats_target than non-indexed columns, this might improve our default behavior without overburdening Analyze. Proposal: That we consider: -- adding a new GUC default_stats_indexed -- that this GUC be set initially to 100 if stats_target is 10 -- that the system be adjusted to that indexed columns take their stats_target from default_stats_indexed and not default_stats_target -- that expressional indexes be ignored for this purpose, as implementation would be too complex, and they have their own stats anyway If this proposal is worth considering, I will spend some time building up a test case to demonstrate the cost and utility of the plan. With Neil's help, of course! -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Timing of 'SELECT 1'
Andreas Pflug wrote: Bruce Momjian wrote: There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case conditions which limits the amount of queries you can fire off in 1 second, no matter how simple. In certain rare cases this is something of a bottleneck. In my personal case it would be nice to see that time lower because converted COBOL applications tend to generate a lot of 'simple' queries. Yes, most of that might be network time. I am using log_duration, which I think just tests backend time, not network transfer time, but I might be wrong. I want to look into this as it seems no one knows the answer. That's easy to verify with standard ping. In my switched 100MBit network, roundtrip for small packets is about 0.2 ms, and 0.5ms for 1kb packets. How about context switch latency? I am on a dual Xeon. I wouldn't think there was that much of a context switch overhead, except for kernel calls. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PITR Functional Design v2 for 7.5
To clarify: I'd expect a cluster to be workable, if I - disable VACUUM until backup completed - issue CHECKPOINT - backup clog (CHECKPOINT and backup clog are the backup checkpoint) - backup all datafiles (which include at least all completed transaction data at checkpoint time) and then - restore datafiles and clog - bring up pgsql. Why is that a useful approach? You might as well shut down the postmaster and do a cold filesystem backup, because you are depending on the data files (including clog) not to change after the checkpoint. You cannot make such an assumption in a running database. I think there is a misunderstanding here. What I think is possible is the following (continuous backup of WAL assumed): - disable VACUUM - issue CHECKPOINT C1 - backup all files - reenable VACUUM - restore files - adapt pg_control (checkpoint C1) - recover WAL until at least end of backup The db is inconsistent until you recovered all WAL (PITR) that accumulated during file backup. I am not sure about clog, isn't clog logged in xlog ? Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] PANIC on start
Hi,
I have a PostgreSQL (7.3) with lots of databases. I can't start it after a
power failure. I´m trying this...
NS2 - /var/lib/pgsql/data pg_ctl start
postmaster successfully started
PANIC: The database cluster was initialized with LC_CTYPE 'pt_BR',
which is not recognized by setlocale().
It looks like you need to initdb.
/usr/bin/pg_ctl: line 392: 569 Aborted $po_path
${1+$@} /dev/null 21
But PostgreSQL doesn't start and the files /tmp/.s.PGSQL.5432,
/tmp/.s.PGSQL.5432.lock and /var/lib/pgsql/postmaster.pid are created.
Does any one could help me ?
Smart Tech Consulting
Marcelo Carvalho Fernandes
Diretor de Sistemas
[EMAIL PROTECTED]
Avenida Rio Branco 181 sala 1005
tel: 55-21-2532-6335
www.smartech.com.br
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] selective statement logging
Bruce Momjian wrote: Andrew Dunstan wrote: The TODO list contains this item which I said I would look at: Allow logging of only data definition(DDL), or DDL and modification statements The trouble I see is that we currently do statement logging before we have examined the query string at all, in the code shown below from src/backend/tcop/postgres.c. I guess I could construct one or more regexes to examine the query string, although that might affect performance a bit (of course, I would precompile the patterns). Any other ideas on how to proceed? Yes, look at how the command tag is grabbed for the PS display, and do the log checks at that point. Yes, I thought about that. But it would delay the logging of statements, and I'm not sure that's a good idea. What would happen on parse errors, for example? cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Timing of 'SELECT 1'
Merlin Moncure kirjutas K, 10.03.2004 kell 17:00: Bruce Momjian wrote: I am timing small queries, and found that a PREPARE/EXECUTE of SELECT 1 takes about 1.2ms on my machine. A normal SELECT doesn't take much longer, so I am wondering why a simpler query isn't faster. Looking at log_executor_stats, I see the following. Execute shows nothing taking much time, mostly .2ms, but the total seems high. I wonder if one of our standard query start/stop functions is taking too long and can be optimized. There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case conditions which limits the amount of queries you can fire off in 1 second, no matter how simple. Have you checked if this is a per-backend or per-connection limit? In certain rare cases this is something of a bottleneck. In my personal case it would be nice to see that time lower because converted COBOL applications tend to generate a lot of 'simple' queries. Merlin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Timing of 'SELECT 1'
Kurt Roeckx [EMAIL PROTECTED] writes: If I do a query on localhost with lots of data, I get a small time in the log, if I do it over a slow link the time get higher. It changes from 1 second to 2 minutes or something. So I think it's until the client has received the data. It'll at least be until the backend has been able to send the data. However, for a small amount of retrieved data, I doubt the kernel will make the backend wait at the send() --- if there even is one before the log entry is made. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] selective statement logging
Andrew Dunstan wrote: Yes, look at how the command tag is grabbed for the PS display, and do the log checks at that point. Yes, I thought about that. But it would delay the logging of statements, and I'm not sure that's a good idea. What would happen on parse errors, for example? Well, if it is parse error, then we can't know what type of command it really was. They could type 'SE9ECT 1' or 'SELECT 1 WH8RE x=1' and both are not really SELECT commands to me. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Slony-I makes progress
Christopher Browne wrote: Further bonus: the GUI project need only have a database connection to one of the databases to control things. No need for ANYTHING else. After fleshing it out a little, that's a pretty slick approach. You miss the point, sorry. This make GUI easy to write approach leads to one major problem. When a central server in the cluster dies and the communication path's need to be redirected and the utility needs to contact all the remaining servers because they're not doing the big group chat always, but now their regular communication path is disrupted ... your GUI (the only thing wannabe-DBA's know) becomes useless and the whole plan with failover and backup systems falls apart. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PITR Functional Design v2 for 7.5
Josh Berkus kirjutas T, 09.03.2004 kell 19:46: In my personal experience, the *primary* use of PITR is recovery from User Error. For example, with one SQL Server 7.0 installation for a law firm, I've made use of PITR 4 times over the last 4 years: once was because and HDD failed, the other three were all becuase of IT dept. staff running unconstrained UPDATE queries against the back end. For recovery with minimal loss of data, there are existing solutions, such as replication servers, in addition to PITR; for recovery from User Error, only PITR will suffice. Actually PostgreSQL used to have very good support for this until some time in Postgres95 development by supporting additional temporal qualifiers for queries. I.e. one could ask for 'SELECT SALARY FROM EMPLOYEES AS IT WAS AT YESTERDAY NOON' (the syntax was not exacly this :) It was a very simple and logical result of PostgreSQL's MVCC storage and was supported by VACUUM allowing dropping only deleted tuples older than some specified time. Oracle has recently added something similar (using their WAL's) to ver. 9.0 or 10.x of their DBMS exactly for recovery from user errors. The support for this was dropped from postgreSQL citing performance reasons at that time, but I still hope that it can restored some time. -- Hannu ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] unsafe floats
When UNSAFE_FLOATS is defined there is a check that float results are within the min and max limits, which excludes values like 'Infinity', '-Infinity' and 'Nan'. Is the above something from the SQL standard or just a bug? The input rules for float8 accepts 'Infinity' as a value, and then it just triggers a overflow error directly after (unless UNSAFE_FLOATS is defined). At first I thought it was a bug, but this function that checks for overflow wouldn't even be needed if not to stop such values. Without the check every possible value would be accepted (on normal IEEE math). I can see a use in not accepting Infinity and Nan, but I would rather put that as constraints if I needed that in my database. Any thoughts? Should I go ahead and make it accept 'Infinity' and the rest as numbers? -- /Dennis Björklund ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] selective statement logging
Bruce Momjian wrote: Andrew Dunstan wrote: Yes, look at how the command tag is grabbed for the PS display, and do the log checks at that point. Yes, I thought about that. But it would delay the logging of statements, and I'm not sure that's a good idea. What would happen on parse errors, for example? Well, if it is parse error, then we can't know what type of command it really was. They could type 'SE9ECT 1' or 'SELECT 1 WH8RE x=1' and both are not really SELECT commands to me. If I delay logging until after the parse, these log lines would come out in reverse order. Not sure if that matters to anyone, but it might annoy me slightly. line:3 LOG: statement: se4dt ddd; line:4 ERROR: syntax error at or near se4dt at character 1 cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] unsafe floats
Dennis Bjorklund [EMAIL PROTECTED] writes: When UNSAFE_FLOATS is defined there is a check that float results are within the min and max limits, which excludes values like 'Infinity', '-Infinity' and 'Nan'. Is the above something from the SQL standard or just a bug? I think it was probably reasonable when the code was written (I'd guess this goes back to very early 90s). Nowadays, IEEE float math is nearly universal, and we would be offering better functionality if we allowed access to Infinity and Nan by default. So I'd vote for ripping out the range check, or at least reversing the default state of UNSAFE_FLOATS. We might end up with two sets of regression expected files, one for machines that do not support NaN, but that seems acceptable to me. A variant idea is to try to get configure to detect Infinity/NaN support and set UNSAFE_FLOATS only if it's there. But I don't know if we can do that without a run-time check, which Peter probably will complain about... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] unsafe floats
Dennis Bjorklund [EMAIL PROTECTED] writes: When UNSAFE_FLOATS is defined there is a check that float results are within the min and max limits, which excludes values like 'Infinity', '-Infinity' and 'Nan'. No, 'NaN' is legal float4/float8/numeric input whether UNSAFE_FLOATS is defined or not. At first I thought it was a bug, but this function that checks for overflow wouldn't even be needed if not to stop such values. Well, CheckFloat4Val() is needed to ensure that the input fits in a 'float' (rather than just a 'double'). Should I go ahead and make it accept 'Infinity' and the rest as numbers? What number would you like 'Infinity'::float4 and 'Infinity'::float8 to produce? Is this actually useful functionality? As for it being in the SQL standard, using Acrobat's text search feature finds zero instances of infinity (case insensitive) in the 200x draft spec. I haven't checked any more thoroughly than that, though. My inclination is to get rid of UNSAFE_FLOATS entirely, and disallow 'Infinity' and '-Infinity' input to float8 (since it never worked to begin with, and float4 doesn't accept those values either). But I'm open to other comments. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] unsafe floats
Tom Lane [EMAIL PROTECTED] writes: Nowadays, IEEE float math is nearly universal, and we would be offering better functionality if we allowed access to Infinity and Nan by default. This is faulty reasoning: we *do* allow NaN by default (although you're correct that we reject Infinity in float8 input, but it seems not by design). So I'd vote for ripping out the range check, or at least reversing the default state of UNSAFE_FLOATS. This would surely be wrong. Defining UNSAFE_FLOATS will make float4in() not check that its input fits into a 'float', rather than a 'double'. We might end up with two sets of regression expected files, one for machines that do not support NaN, but that seems acceptable to me. Are there any modern machines that actually do not support NAN? There are various places in the code that do #ifndef NAN #define NAN (0.0/0.0) #endif ... and this hasn't caused any problems that I'm aware of. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] unsafe floats
On Wed, 10 Mar 2004, Neil Conway wrote: No, 'NaN' is legal float4/float8/numeric input whether UNSAFE_FLOATS is defined or not. Yes, the tests are: if (fabs(val) FLOAT8_MAX) if (val != 0.0 fabs(val) FLOAT8_MIN) and only infinity and not NaN will trigger the overflow. I read it wrong first. Well, CheckFloat4Val() is needed to ensure that the input fits in a 'float' (rather than just a 'double'). Sure, for Float4 (maybe working with float in C instead of double and this check would make a difference, but I havn't really thought about that). What number would you like 'Infinity'::float4 and 'Infinity'::float8 to produce? Is this actually useful functionality? I would like them to produce the IEEE 754 number 'infinity' (usually writte 'Inf' in other languages). As for it being in the SQL standard, using Acrobat's text search feature finds zero instances of infinity (case insensitive) in the 200x draft spec. I haven't checked any more thoroughly than that, though. If they say that it should use IEEE 754 math, then they do say that Infinity is a number, just like it is in C and lots of other languages with IEEE 754 math. Being as old as it is, I would guess that the standard doesn't really say much at all about floats. Why should pg not do the same as most (all?) other language that use IEEE 754 math? -- /Dennis Björklund ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] selective statement logging
Andrew Dunstan wrote: Sure you sure? I didn't think you would get a tag on a syntax error, so no log would print, which I think is OK. If people are happy with suppressing statement logging on a parse error, OK. For the remainder I would just defer the logging till immediately after the parse and get the tags for the statements - and suppress logging the query string if they were all SELECT or all SELECT|INSERT|UPDATE|DELETE|COPY according to the setting. Good. My feeling is that if there is a parse error, we can't be sure what type of query was sent, and it isn't going to be executed anyway because it threw an error. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] unsafe floats
Neil Conway [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: So I'd vote for ripping out the range check, or at least reversing the default state of UNSAFE_FLOATS. This would surely be wrong. Defining UNSAFE_FLOATS will make float4in() not check that its input fits into a 'float', rather than a 'double'. Possibly the appropriate test involves using isfinite() (apparently spelled finite() some places, but the C99 spec says isfinite). If that returns false, take the value as good without checking range. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] unsafe floats
Neil Conway [EMAIL PROTECTED] writes: What number would you like 'Infinity'::float4 and 'Infinity'::float8 to produce? Is this actually useful functionality? On an IEEE-spec machine, it's highly useful functionality. +Infinity and -Infinity are special values. BTW the float4out and float8out routines already cope with NANs and infinities, so ISTM the input routines should be able to reverse that. (At the moment you might only be able to get an infinity inside the system as the result of certain math functions.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] selective statement logging
Andrew Dunstan [EMAIL PROTECTED] writes: If people are happy with suppressing statement logging on a parse error, OK. I think that's a really, really awful idea. Not infrequently, the postmaster log is the easiest way of debugging applications that are sending bogus SQL. If you fail to log the bogus SQL then you've just cut debuggers off at the knees. I haven't read the earlier part of the thread yet so I don't know just what problem you want to solve, but please not this solution. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How to get RelationName ??
On Wednesday 10 March 2004 09:58 am, Ramanujam H S Iyengar wrote: Hello, How can i get the name of a relation from its Oid ?? I have seen some functions in utils/cache/relcache.h like RelationIdGetRelation - which gives Relation Node from Oid RelationSysNameGetRelation - which gives Relation node for only the System names. Can anyone please tell me how to get Relation node from relation name ??? Won't you have to go get the data from the database for this? I mean actually execute the query: select relname from pg_class where oid = 1234; -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] selective statement logging
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: If people are happy with suppressing statement logging on a parse error, OK. I think that's a really, really awful idea. Not infrequently, the postmaster log is the easiest way of debugging applications that are sending bogus SQL. If you fail to log the bogus SQL then you've just cut debuggers off at the knees. I haven't read the earlier part of the thread yet so I don't know just what problem you want to solve, but please not this solution. The issue is allowing only logging of DDL statements, or DDL and data modification statements, as listed on the TODO list. If they ask for all statements, certainly we should log all statements. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] selective statement logging
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: If people are happy with suppressing statement logging on a parse error, OK. I think that's a really, really awful idea. Not infrequently, the postmaster log is the easiest way of debugging applications that are sending bogus SQL. If you fail to log the bogus SQL then you've just cut debuggers off at the knees. I haven't read the earlier part of the thread yet so I don't know just what problem you want to solve, but please not this solution. I had a small bet with myself that you'd say that :-) I agree with you. Actually, I think I can improve the present situation. Currently, if log_statement is not turned on and you send a query that doesn't parse, all you get is the error trace. By deferring it till right after the parse we can force logging of the query string on a parse error, regardless of that setting (which seems to me to be a very desirable outcome). The only thing is that you will get the error trace first (because it comes from the parser) rather than the query string first. That should keep you happy, I hope ;-) (The problem being addressed in this thread is to allow selective logging of DDL/DML statements - see the TODO list. Someone was actually asking for exactly this on irc today.). cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Shouldn't B'1' = 1::bit be true?
Thomas Swan [EMAIL PROTECTED] writes: To convert low bits ot high bits you pad 0 bits on the left. To convert from high to low you strip bits off the left hand side. This allows reasonable behavior. Unfortunately, the SQL spec is perfectly clear that you pad or strip zero bits on the *right* of the bit string. We cannot change that. It might have been better if we had defined int-bit casts to treat the first bit of the bit string as the LSB of the integer. But we didn't, and it's probably too big a change to consider. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Shouldn't B'1' = 1::bit be true?
quote who=Tom Lane Bill Moran [EMAIL PROTECTED] writes: Am I missing something here? Hmm. It seems like int-to-bit casting ought to be aware of the bit-width one is casting to, and take that number of bits from the right end of the integer. This would make it be the inverse of the other direction. Right now it's only an inverse when you cast to and from bit(32). For shorter bitfield widths, we're effectively inserting at the right end of the integer, but removing bits from the left, which is not consistent. regression=# select B'11000'::bit(5)::int; int4 -- 24 (1 row) regression=# select 24::int::bit(32); bit -- 00011000 (1 row) regression=# select 24::int::bit(32)::bit(5); bit --- 0 (1 row) regression=# select 24::int::bit(5); bit --- 0 (1 row) If we made int-to-bit-N take the rightmost N bits, then the last two cases would yield different results, but that doesn't seem unreasonable to me. Or at least it's less unreasonable than bit(5)-to-int not being the inverse of int-to-bit(5). Comments? Tom, I would suggest looking at char to int to char conversions for correctness examples. This can be looked at as bit(8)::bit(32)::bit(8) operations. To convert low bits ot high bits you pad 0 bits on the left. To convert from high to low you strip bits off the left hand side. This allows reasonable behavior. Coverting from low precision to high precision and back to low precision should be lossless for bits. High to low to high should be lossy for bits because you lost bits in the smaller container. So the conversion should be symmetric for values that fit in the bitspace. Even the MS calculator does this. 65534::bit(8)::int should yield 254 just as 254::int::bit(8) should be 254 and 254::int::bit(32)::bit(8)::int should yield 254. I would say the current way of handling bit conversions is broken. I for one would like to see the bitwise behavior be more correct. It's almost as if the bit(32)::bit(8) operation is thinking of string truncation. Is this endian related? Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] selective statement logging
Andrew Dunstan wrote: Actually, I think I can improve the present situation. Currently, if log_statement is not turned on and you send a query that doesn't parse, all you get is the error trace. By deferring it till right after the parse we can force logging of the query string on a parse error, regardless of that setting (which seems to me to be a very desirable outcome). The only thing is that you will get the error trace first (because it comes from the parser) rather than the query string first. Or I would do except that I see that a parse error causes a longjmp out of that code. I will see if I can come up with a way around that. If not, I think we'd better let sleeping dogs lie. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq thread safety
Bruce Momjian wrote: Manfred Spraul wrote: Hi, I've searched through libpq and looked for global or static variables as indicators of non-threadsafe code. I found: - Win32 and BeOS: there is a global ioctlsocket_ret variable, but it seems to be a dummy variable that is always discarded. Right, and it is moving into a compatibility function in 7.5 where it will be a local function variable. Done. - pg_krb4_init(): Are the kerberos libraries thread safe? Additionally, setting init_done is racy. No idea. - pg_krb4_authname(): uses a static buffer. - kerberos 5: Is the library thread safe? the initialization could run twice, I'm not sure if that's intentional. - pg_krb4_authname(): relies on the global variable pg_krb5_name. Seems kerberos isn't. - PQoidStatus: uses a static buffer. Yes, known documented problem. - libpq_gettext: setting already_bound is racy. Does that happen in different threads? - openssl: According to http://www.openssl.org/docs/crypto/threads.html libpq must register locking callbacks within openssl, otherwise there will be random corruptions. Additionally the SSL_context initialization is not properly synchronized, and SSLerrmessage relies on a static buffer. Oh. PQoidStatus is already documented as not thread safe, but what about OpenSSL and kerberos? It seems openssl needs support with callbacks, and according to google searches MIT kerberos 5 is not thread safe, and libpq must use mutexes to prevent concurrent calls into the kerberos library. Oh, seems like a TODO here. We already know how to do thread locking in port/thread.c so maybe we just need to add some locks in there. What killed the idea of doing ssl or kerberos locking inside libpq was that there was no way to be sure that outside code didn't also access those routines. I have documented that SSL and Kerberos are not thread-safe in the libpq docs. Let's wait and see If we need additional work in this area. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] why not ADTs?
Hi,everyone, I am afraid why not make postgreSQL support ADTs such as bag,list and so on.In mymind,all these will make the system more diversiform,aren't they? Thanks! == 263
[HACKERS] Timezone support
As some may remember, there has been talk about shipping our own timezone implementation with PostgreSQL. This will remove reliance on possible broken timezone OS implementations, and give us added capabilities. The Win32 port will also need this functionality. SRA has implemented similar functionality in their threaded win32 port using David Olson's timezone library, and I have gotten permission to share that code with the community. If someone wants to start hacking on that, I will send them a tar file containing the SRA timezone code. The SRA code is actually multi-threaded, so you will have to pull pieces out of their changes. However, it will give coders a nice sample implementation. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] selective statement logging
Bruce Momjian [EMAIL PROTECTED] writes: The issue is allowing only logging of DDL statements, or DDL and data modification statements, as listed on the TODO list. If they ask for all statements, certainly we should log all statements. just make syntax errors one of the types. So you could log ddl, dml, syntax errors, or all statements. In fact for production use syntax errors seem like the most likely thing to want to log. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] How to get RelationName ??
Hello, Sorry for the previous confusing mail .. iam in need of a function through which i can get a Relation Node given its name .. precisely the same one equivalent to RelationSysNameGetRelation .. which works for all types of relations (system and user tables) On Wednesday 10 March 2004 09:58 am, Ramanujam H S Iyengar wrote: Hello, How can i get the name of a relation from its Oid ?? I have seen some functions in utils/cache/relcache.h like RelationIdGetRelation - which gives Relation Node from Oid RelationSysNameGetRelation - which gives Relation node for only the System names. Can anyone please tell me how to get Relation node from relation name ??? Won't you have to go get the data from the database for this? I mean actually execute the query: Iam trying to test for some patch in the code of the Optimizer(planner) and so I need some C functions, which does the job. I think we must have some functions like the ones i have mentioned for the purpose. isin't it ?? regards, -Ramu select relname from pg_class where oid = 1234; -- Jonathan Gardner jgardner ( at ) jonathangardner ( dot ) net _ Need a job? Get head-hunted by the best. http://www.naukri.com/msn/index.php?source=hotmail Post your CV free! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] How to get RelationName ??
Ramanujam H S Iyengar [EMAIL PROTECTED] writes: iam in need of a function through which i can get a Relation Node given its name .. precisely the same one equivalent to RelationSysNameGetRelation .. which works for all types of relations (system and user tables) The reason RelationSysNameGetRelation only works for system relations is that it assumes the schema name is pg_catalog. For non-system relations you cannot do a lookup based only on relation name, because there's no certainty that the result is unique. There are some functions in catalog/namespace.c that can do lookup of an unqualified name relative to the current schema search path, if that's what you want. Iam trying to test for some patch in the code of the Optimizer(planner) and so I need some C functions, which does the job. The optimizer has no need, ever, to find a relation by name; all it ever sees are predigested relation OIDs. So you are not making a lot of sense here. You certainly cannot assume that a search-path lookup is appropriate for a relation that the optimizer is dealing with. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] grants
On Wed, 10 Mar 2004, Andreas Pflug wrote:
Kris Jurka wrote:
On Wed, 10 Mar 2004, Andreas Pflug wrote:
The problem that cannot be solved with either this or a function that
loops and grants on each table is that it is not a permanent grant of what
the admin had in mind. If a new table is added or an existing table is
dropped and recreated, the grants must be done again. The real use of a
SELECT ANY TABLE permission is ignorance of schema updates.
Hm, does this exist in other DBMS?
As soon as roles are implemented, there might be a default role
('public') for this. Until then, using groups solves most of the
problems (well, you certainly still need to GRANT rights to your
preferred group).
Groups help, but only if you want to GRANT to more than one user, and you
still need to do it on after schema changes. I know this is implemented
in at least Oracle, SELECT ANY TABLE is in fact the permission
name used.
Kris Jurka
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] unsafe floats
On Thu, 11 Mar 2004, Neil Conway wrote: So, what is the correct behavior: if you multiply two values and get a result that exceeds the range of a float8, should you get 'Infinity'/'-Infinity', or an overflow error? That's the issue and I think we should allow infinity as a result of a float operation (like you got in the example). It's part of IEEE 754 math, so not getting Infinity as a result would break that. If someone does not want infinity stored in a column he/she could add a constraint to disallow it. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] unsafe floats
On Thu, 11 Mar 2004, Neil Conway wrote: Fair enough. Attached is a patch that implements this. I chose to remove UNSAFE_FLOATS: if anyone thinks that is worth keeping, speak up now. I have one question about the use of HUGE_VAL in postgresql. I got the impression that the whole thing was designed to work on computers and compilers where there is no infinity value, and then HUGE_VAL is defined as the biggest number and treated as a special value. If that is the case then using isinf() would not work (unless we have our own). Well, maybe it's not an issue at all. Everything is IEEE 754 anyway today. A more important question is if we should give errors or produce Infinity and NaN on mathematical operations. That is, should operations like sqrt(-1.0) produce NaN or give an error. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PANIC on start
Marcelo Carvalho Fernandes [EMAIL PROTECTED] writes: I have a PostgreSQL (7.3) with lots of databases. I can't start it after a power failure. I´m trying this... NS2 - /var/lib/pgsql/data pg_ctl start postmaster successfully started PANIC: The database cluster was initialized with LC_CTYPE 'pt_BR', which is not recognized by setlocale(). Kinda looks like your locale definition file has gone missing. Probably reinstalling the locale stuff would help. (On Red Hat Linux I think this is part of the glibc-common RPM ... but YMMV on other platforms.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Functional Design v2 for 7.5
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: What I think is possible is the following (continuous backup of WAL assumed): - disable VACUUM - issue CHECKPOINT C1 - backup all files - reenable VACUUM - restore files - adapt pg_control (checkpoint C1) - recover WAL until at least end of backup I do not understand this fixation on disable VACUUM. What are you thinking that will buy you? AFAICS it would make no difference. I am not sure about clog, isn't clog logged in xlog ? Right. For the purposes of PITR, clog behaves the same as regular data files. You gotta back it up, but a filesystem copy will be inconsistent until fixed by WAL replay. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] How to get RelationName ??
Ramanujam H S Iyengar hals_ramu ( at ) hotmail ( dot ) com writes: iam in need of a function through which i can get a Relation Node given its name .. precisely the same one equivalent to RelationSysNameGetRelation .. which works for all types of relations (system and user tables) The reason RelationSysNameGetRelation only works for system relations is that it assumes the schema name is pg_catalog. For non-system relations you cannot do a lookup based only on relation name, because there's no certainty that the result is unique. There are some functions in catalog/namespace.c that can do lookup of an unqualified name relative to the current schema search path, if that's what you want. Thanks a lot, The function RelnameGetRelid(const char *) is working for me !! Iam trying to test for some patch in the code of the Optimizer(planner) and so I need some C functions, which does the job. The optimizer has no need, ever, to find a relation by name; all it ever sees are predigested relation OIDs. So you are not making a lot of sense here. You certainly cannot assume that a search-path lookup is appropriate for a relation that the optimizer is dealing with. regards, tom lane Iam trying to put in some other module of optimizer that we have developed, which works on relation name and gives the optimal plan. Iam trying to convert the plans given by this(our) optimizer to the Plan structure of PostgreSQL. So finally i have to convert the Relation names back to their relOids. All this in the process of using the execution engine of Postgres for our optimizer module !! :-) Thats why i was asking !! Thanks -Ramu _ Take a loan. Win great prizes! Handsome prizes to be won! Take a loan win TV, Fridge many more prizes ! http://go.msnserver.com/IN/44044.asp ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
