Re: [HACKERS] friday 13 bug?
zohn_ming wu wrote: swap_free: Bad swap file entry 0004 Do you use ECC memory, is ECC enabled in the BIOS [and does it work - some vendors lie about ECC support]? I would bet that it's a soft memory error: means not used. One bit differs, and the kernel complains about the invalid value. I think the following oops is a side effect of the bad swap entry. Do you have timestaps in the system log? Is the swap error just before the BUG in buffer.c? -- Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-hackers-win32] Sync vs. fsync during checkpoint
Tom Lane wrote: You can only fsync one FD at a time (too bad ... if there were a multi-file-fsync API it'd solve the overspecified-write-ordering issue). What about aio_fsync()? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers-win32] Sync vs. fsync during checkpoint
Florian Weimer [EMAIL PROTECTED] writes: Tom Lane wrote: You can only fsync one FD at a time (too bad ... if there were a multi-file-fsync API it'd solve the overspecified-write-ordering issue). What about aio_fsync()? (1) it's unportable; (2) it's not clear that it's any improvement over fsync(). The Single Unix Spec says aio_fsync returns when the synchronisation request has been initiated or queued to the file or device. Depending on how the implementation works, this may mean that all the dirty blocks have been scheduled for I/O and will be written ahead of subsequently scheduled blocks --- if so, the results are not really different from fsync()'ing the files in the same order. The best idea I've heard so far is the one about sync() followed by a bunch of fsync()s. That seems to be correct, efficient, and dependent only on very-long-established Unix semantics. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] 7.4 - FK constraint performance
On Fri, 13 Feb 2004, Stephan Szabo wrote: On Fri, 13 Feb 2004, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Fri, 13 Feb 2004, Tom Lane wrote: I was looking at that last night. It seems like we could add a LIMIT at least in some contexts. In the case at hand, we're just going to error out immediately if we find a matching row, and so there's no need for FOR UPDATE, is there? I think there still is, because a not yet committed transaction could have deleted them all in which case I think the correct behavior is to wait and if that transaction commits allow the action and if it rolls back to error. Good point. Okay, we can't put in a LIMIT. But we could still hack the planner to prefer a fast-start plan by passing an out-of-band tuple fraction, for those RI plans where it's appropriate. That would not affect correctness. Right, I can try to look through the stuff you pointed at in the previous message over the weekend. It looks to me that we could make this available to SPI fairly simply by taking the current version of the following four routines: planner, pg_plan_query, _SPI_execute and SPI_prepare, renaming them and giving them a planning tuple fraction as a parameter, change references to the other routines to the new names and then making four new functions with the current names that call the renamed versions. In all the cases other than planner I think we can have the new version pass 0.0 and in the case of planner either 0.1 or 0.0 based on the isCursor parameter. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Security documentation
On Wednesday 11 February 2004 12:46, Jim C. Nasby wrote:
On Sun, Feb 08, 2004 at 11:24:56PM -0800, Josh Berkus wrote:
The problem with this approach, of course, is that large application
developers generally like to make the database fairly passive and put
all business security logic in the middleware. I do think it would be
useful for them to realize that they are sacrificing a significant
portion of their data security by doing so.
Perhaps what would be best is some kind of a 'best practices' guide.
There's far more that people should consider beyond just quoting
strings; Josh's example is just one thing.
If written carefully, such a guide could serve both experienced DBAs as
well as people who are very new to databases, since every database has
it's own prefered way of doing things.
Was thinking if somene want to write up a series of articles discussing
security best practices, this might be a good starting point since it would
require somone to have everything figured out before getting started; you
could pick a certain section and get specific about it. We have the
infrastructure on techdocs to publish this, and once started we could use it
to determine what should or should not be added to the standard docs.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Proposed Query Planner TODO items
I wrote: I see what is going on to make Q4 slow, too. It's this: where o_orderdate = date '1995-04-01' and o_orderdate date '1995-04-01' + interval '3 month' ... As of CVS tip the issue could be eliminated by introducing cross-data-type comparison operators between types date and timestamp without time zone, and then making these be members of the date index opclass. I'm strongly tempted to do so ... I have now done this, so if you care to re-sync with CVS tip you should find that the queries using this sort of date constraint go faster. (You do have indexes on all the date columns, no?) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Please help re function packaging...
Anyone please help... I'm a newbie on creating functions in postgresql.
Here is an oracle package that I'm trying to port to postgresql:
CREATE OR REPLACE PACKAGE BODY NewsPkg.NewsTools
AS
PROCEDURE del_news (i_id IN VARCHAR2)
IS
BEGIN
DELETE FROM tbl_news_type
WHERE uniqueid = i_id;
DELETE FROM tbl_news
WHERE uniqueid = i_id;
END del_news;
PROCEDURE upd_newstype
IS
CURSOR cur_news
IS
SELECT uniqueid
FROM tbl_news_type
WHERE mother_id IS NULL
GROUP BY uniqueid;
v_id tbl_news_type.uniqueid%TYPE;
BEGIN
OPEN cur_news;
LOOP
FETCH cur_news INTO v_id;
upd_mothernews (v_id);
COMMIT;
END LOOP;
CLOSE cur_news;
END upd_newstype;
END NewsTools;
Can anyone help me on how this oracle package would look like in
postgresql... I would really appreciate it if someone could show me
even the synopsis of this code in postgresql. I've been trying all
sorts of ways to come up with the code in postgresql but when I this
command:
select NewsTools.del_news('20040111DN001869');
I get this error:
ERROR: Namespace "NewsTools" does not exist
Tnx,
Darius
Re: [HACKERS] Two-phase commit
On Sun, 8 Feb 2004, Jeroen T. Vermeulen wrote: On Wed, Feb 04, 2004 at 10:22:16PM +0200, Heikki Linnakangas wrote: There is a system view pg_prepared_xacts that gives you all transactions that are in prepared state waiting for COMMITPREPARED or ABORTPREPARED. Great to hear that you've gotten so far with this... One question: can I check for this view to see if 2PC is supported before issuing the new kind of commit? I'm interested in supporting 2PC even for some regular transactions to reduce their in-doubt window, but I don't want to issue a command at the last moment that may fail (and thereby abort) because the backend version I'm connected to doesn't support the new command! Yes, I suppose that would work. Though you would have to use a query that wouldn't fail in case the view doesn't exist, otherwise you end up aborting the transaction anyway. This should work: SELECT COUNT(*) FROM pg_views WHERE schemanem='pg_catalog' AND viewname ='pg_prepared_xacts' If it returns 1, you can do 2PC, if it returns 0, you have to regular commit. However, if this gets into 7.5, I guess you could just check for the version of the backend instead with SELECT version(). - Heikki ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposed Query Planner TODO items
On Mon, 2004-02-09 at 16:53, Josh Berkus wrote:
Jenny,
For 19, we moved the common conditions out of the big ORs, for 20, we
added distinct. We can change the query back if the optimizer can
handle it now.
Well, we want to test if it can.
Replace the file 19.sql under datagen/pgsql-queries with the attachment
should do it.
Jenny
-- @(#)19.sql 2.1.8.1
-- TPC-H/TPC-R Discounted Revenue Query (Q19)
-- Functional Query Definition
-- Approved February 1998
:b
:x
:o
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = ':1'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity = :4 and l_quantity = :4+10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = ':2'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity = :5 and l_quantity = :5+10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = ':3'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity = :6 and l_quantity = :6+10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
:e
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] No Timeout in SELECT..FOR UPDATE
Hi Guys, I have a suggestion for fixing a long-term and painful problem in PostgreSQL that is holding up many very important commercial projects, including ours! This problem has been reported numerous times: When one process has a row lock on one or more rows in a table, using SELECT...FOR UPDATE in default lock mode, another process has NO WAY of aborting from the same request, and reporting to the user that this record is already locked, reserved, or whatever you want to call it. In other words, by the time the second process has run the SELECT...FOR UPDATE statement, it's too late!! This second process is now locked forever, waiting for the first process to commit, rollback, or some other function that will release the lock. Yes, the first process will eventually release the lock by commiting or rollback, bu this is for a commercial environment with users, not processes, and the user needs to be informed about the error immediately, or within a second or so, and be given the chance to retry the update with lock, or just abort and go find another record to change. This problem is *fundamental*, and *very typical* in a commercial, accounting, or mission-critical environment. The only solution to this problem in PostgreSQL seems to be to: (1) Re-write the SELECT...FOR UPDATE SQL code, to return with an exception or error if it cannot immediately secure the lock, OR: (2) Add a TIMEOUT N clause to this statement, so that the timeout can be controlled on a per-statement basis - this is probably better. For example: [1] if I want the second process to give up within 1 millisecond, and return if the lock is not possible, then write: SELECT ... FOR UPDATE TIMEOUT 1 [0] If I want the default behaviour, (for a process that is prepared to wait forever for the record(s)), then: SELECT... FOR UPDATE TIMEOUT 0 OR, simply: SELECT... FOR UPDATE (as it is now) I hope that this suggestion will be taken seriously, since it is clear that a large number of developpers have made comments on this problem, dated all the way back to 2001 or earlier. Many thanks, Tony Rich, Richcorp Technology, Sydney, Australia. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] No Timeout in SELECT..FOR UPDATE
Anthony, What you need is a NO WAIT option. This is already on the TODO list. This feature should be implemented as GUC (see TODO list). I don't think that a timeout would be accepted by the core team (doesn't make too much sense to me either). Telling PostgreSQL not to wait for certain locks is definitely better (at least from my point of view). We might work on a patch like that in the near future but don't hesitate to send a patch yourself. Best regards, Hans Anthony Rich wrote: Hi Guys, I have a suggestion for fixing a long-term and painful problem in PostgreSQL that is holding up many very important commercial projects, including ours! This problem has been reported numerous times: When one process has a row lock on one or more rows in a table, using SELECT...FOR UPDATE in default lock mode, another process has NO WAY of aborting from the same request, and reporting to the user that this record is already locked, reserved, or whatever you want to call it. In other words, by the time the second process has run the SELECT...FOR UPDATE statement, it's too late!! This second process is now locked forever, waiting for the first process to commit, rollback, or some other function that will release the lock. Yes, the first process will eventually release the lock by commiting or rollback, bu this is for a commercial environment with users, not processes, and the user needs to be informed about the error immediately, or within a second or so, and be given the chance to retry the update with lock, or just abort and go find another record to change. This problem is *fundamental*, and *very typical* in a commercial, accounting, or mission-critical environment. The only solution to this problem in PostgreSQL seems to be to: (1) Re-write the SELECT...FOR UPDATE SQL code, to return with an exception or error if it cannot immediately secure the lock, OR: (2) Add a TIMEOUT N clause to this statement, so that the timeout can be controlled on a per-statement basis - this is probably better. For example: [1] if I want the second process to give up within 1 millisecond, and return if the lock is not possible, then write: SELECT ... FOR UPDATE TIMEOUT 1 [0] If I want the default behaviour, (for a process that is prepared to wait forever for the record(s)), then: SELECT... FOR UPDATE TIMEOUT 0 OR, simply: SELECT... FOR UPDATE (as it is now) I hope that this suggestion will be taken seriously, since it is clear that a large number of developpers have made comments on this problem, dated all the way back to 2001 or earlier. Many thanks, Tony Rich, Richcorp Technology, Sydney, Australia. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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: [PATCHES] [HACKERS] dollar quoting
Another interesting thing abut psql that I noticed when using '$' in identifiers is this effect: andrew=# create table ab$cd$ef (ef$cd$ab text); CREATE TABLE andrew=# \d ab$cd$ef Did not find any relation named ab$cd$ef. andrew=# \d ab\$cd\$ef Table public.ab$cd$ef Column | Type | Modifiers --+--+--- ef$cd$ab | text | which is perhaps slightly less than intuitive. cheers andrew ---(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] No Timeout in SELECT..FOR UPDATE
On Tue, 10 Feb 2004, Anthony Rich wrote: In other words, by the time the second process has run the SELECT...FOR UPDATE statement, it's too late!! This second process is now locked forever, waiting for the Or until statement_timeout is reached if it's set to a non-zero value. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] No Timeout in SELECT..FOR UPDATE
Anthony Rich [EMAIL PROTECTED] writes: When one process has a row lock on one or more rows in a table, using SELECT...FOR UPDATE in default lock mode, another process has NO WAY of aborting from the same request, and reporting to the user that this record is already locked, reserved, or whatever you want to call it. Not so. See the statement_timeout parameter. 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: [PATCHES] [HACKERS] dollar quoting
Andrew Dunstan [EMAIL PROTECTED] writes: andrew=# create table ab$cd$ef (ef$cd$ab text); CREATE TABLE andrew=# \d ab$cd$ef Did not find any relation named ab$cd$ef. Hmph. I always thought that $ was only special at the end of a regex, but that doesn't seem to be how our implementation treats it. Anyway this is not a bug, it is a feature: the argument of \d is a regex. 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] No Timeout in SELECT..FOR UPDATE
(1) Re-write the SELECT...FOR UPDATE SQL code, to return with an exception or error if it cannot immediately secure the lock, OR: You could use SET STATEMENT_TIMEOUT... Chris ---(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] Concurrence GiST
Hey Teodor, How's this going? I think you were looking at the same paper I was reading about GiST indexes. I found the GiST source code somewhat over my head, however. I hope you'll still working on it and haven't given up! Chris Teodor Sigaev wrote: Hi! I'll have time and wish to work on concurrence GiST during january. Now I am reading some paper about this and looking into code of postgres for lock management. As I see, postgres doesn't support intentional lock. Is it right? or I missed something... I can use NSN (node sequence number) and I find recommendation to use LSN (WAL log sequence number) as NSN. NSN must be stored in page and I found that page (PageHeaderData struct) already has XLogRecPtr for storing LSN. My question is: who is manage this field? Is it filled automatically or I should write code to manage it? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] dollar quoting
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
Tom Lane wrote:
... But how about
42$foo$
This is a syntax error in 7.4, and we propose to redefine it as an
integer literal '42' followed by a dollar-quote start symbol.
The test should not succeed anywhere in the string '42$foo$'.
No, it won't. The problem is that it should, because the backend will
see that as '42' followed by a $foo$ quote start.
Ok, I see what you are saying. This mismatch would only happen on
invalid input, though. I believe that what I did will work on all legal
input.
I think that this might be cured by having psql recognise a legal
identifier or keyword and eating it as a word, rather than treating it
as just another set of bytes in the stream. That would enable us to
avoid the lookback in the dollar-quote recognition test altogether. The
attached patch does it that way - the keyword/id test needs to come
right at the end of the loop to avoid clashing with backslash commands,
btw.
I *think* that this way psql will recognise the start of a dollar quote
iff the backend lexer would.
Interacting with lexer states would probably be ... unpleasant. Matching
a stream oriented lexer with a line oriented CLI would be messy I suspect.
I think it would not be that bad. We'd have to run the lexer on the
command input buffer and see what state it terminates in.
Yeah. I am not enough of a flex wizard to undertake the task, though. It
would take me lots of time. If we make a decision that we really need
this in order to do dollar quoting in psql I would need some substantial
help, at least.
cheers
andrew
Index: src/bin/psql/mainloop.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/mainloop.c,v
retrieving revision 1.61
diff -c -r1.61 mainloop.c
*** src/bin/psql/mainloop.c 25 Jan 2004 03:07:22 - 1.61
--- src/bin/psql/mainloop.c 15 Feb 2004 14:28:02 -
***
*** 21,26
--- 21,61
sigjmp_bufmain_loop_jmp;
#endif
+ /*
+ * function to detect a valid $foo$ quote delimiter at the start of the
+ * parameter dquote.
+ */
+
+ static bool valid_dolquote(char * dquote)
+ {
+ int i;
+
+ /* must start with a $ */
+ if (dquote[0] != '$')
+ return false;
+
+ /* empty 'identifier' case */
+ if (dquote[1] == '$')
+ return true;
+
+ /* first 'identifier' char must be a letter or have high bit set */
+ if (!isalpha(dquote[1]) (dquote[1] 0x80) == 0)
+ return false;
+
+ /* subsequent chars must be alphanumeric or _ or have high bit set */
+ for (i = 2; dquote[i] != '$'; i++)
+ {
+ if ((dquote[i] 0x80) == 0 ! isalnum(dquote[i])
+ dquote[i] != '_')
+ {
+ /* we found an invalid character */
+ return false;
+ }
+ }
+
+ return true;
+ }
+
/*
* Main processing loop for reading lines of input
***
*** 49,54
--- 84,92
unsigned int query_start;
volatile int count_eof = 0;
volatile unsigned int bslash_count = 0;
+ volatile bool free_dolquote = false;
+ char *dol_quote = NULL;
+
int i,
prevlen,
***
*** 120,125
--- 158,164
in_quote = 0;
paren_level = 0;
count_eof = 0;
+ free_dolquote = true;
slashCmdStatus = CMD_UNKNOWN;
}
else
***
*** 136,141
--- 175,190
pqsignal(SIGINT, handle_sigint);/* control-C = cancel
*/
#endif /* not WIN32 */
+ if (free_dolquote)
+ {
+ if(dol_quote)
+ {
+ free(dol_quote);
+ dol_quote = NULL;
+ }
+ free_dolquote = false;
+ }
+
fflush(stdout);
if (slashCmdStatus == CMD_NEWEDIT)
***
*** 150,155
--- 199,209
in_xcomment = 0;
in_quote = 0;
paren_level = 0;
+ if(dol_quote)
+ {
+ free(dol_quote);
+ dol_quote = NULL;
+ }
slashCmdStatus = CMD_UNKNOWN;
}
***
*** 161,167
{
int prompt_status;
! if (in_quote in_quote == '\'')
Re: [PATCHES] [HACKERS] dollar quoting
Andrew Dunstan [EMAIL PROTECTED] writes: No, it won't. The problem is that it should, because the backend will see that as '42' followed by a $foo$ quote start. Ok, I see what you are saying. This mismatch would only happen on invalid input, though. I believe that what I did will work on all legal input. I'm unconvinced. Even if there are not any current syntaxes in which a numeric literal can be adjacent to a string literal (I'm not totally sure about that), what of the future? We should solve the problem rather than assuming it won't bite us. I think that this might be cured by having psql recognise a legal identifier or keyword and eating it as a word, rather than treating it as just another set of bytes in the stream. Hm, might work ... will think about it ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
