[SQL] have you feel anything when you read this ?
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html --- cut --- mysql no longer terminates data value display when it encounters a NUL byte. Instead, it displays NUL bytes as spaces. (Bug #16859) --- cut --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
have you feel anything when you read this ? Business as usual... It's more fun to grep "crash" on this page, which gets about 27 results... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
PFC wrote: have you feel anything when you read this ? Business as usual... It's more fun to grep "crash" on this page, which gets about 27 results... i am not trying to fight against or for any brandname: not Mesql nor postgres. just sed 's/MySQL/SomeDBMS/g' and concentrate on the fact: that even so stupid DBMS handling NULs properly. :-) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > PFC wrote: > >> have you feel anything when you read this ? > > > > Business as usual... > > > > It's more fun to grep "crash" on this page, which gets about 27 > > results... > > i am not trying to fight against or for any brandname: not Mesql nor > postgres. > > just sed 's/MySQL/SomeDBMS/g' and concentrate on the fact: > that even so stupid DBMS handling NULs properly. :-) So printing a space is "properly"? Curious ... -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Peter Eisentraut wrote: Eugene E. wrote: PFC wrote: have you feel anything when you read this ? Business as usual... It's more fun to grep "crash" on this page, which gets about 27 results... i am not trying to fight against or for any brandname: not Mesql nor postgres. just sed 's/MySQL/SomeDBMS/g' and concentrate on the fact: that even so stupid DBMS handling NULs properly. :-) So printing a space is "properly"? Curious ... you may decide to print something else, aint'you ? BUT if they print them then they at least OUTPUT them. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > you may decide to print something else, aint'you ? > BUT > if they print them then they at least OUTPUT them. I'm not sure what you are getting at here. The only data type in PostgreSQL that has a notion of null bytes is bytea, and bytea prints out null bytes in unambigious form. Note that printing out a space will lose the null byte on restore, so that solution does not seem satisfactory. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Peter Eisentraut wrote: Eugene E. wrote: you may decide to print something else, aint'you ? BUT if they print them then they at least OUTPUT them. I'm not sure what you are getting at here. The only data type in PostgreSQL that has a notion of null bytes is bytea, and bytea prints out null bytes in unambigious form. the bytea does not output NULs at all. don't mock me. Note that printing out a space will lose the null byte on restore, ok, if you or they or me miscall OUTPUT "the printing" then "print" NUL-byte itself to preserve it on restore. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote:
> the bytea does not output NULs at all.
> don't mock me.
peter=# create table test (a bytea);
CREATE TABLE
peter=# insert into test values ('a\\000b');
INSERT 0 1
peter=# select * from test;
a
a\000b
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
O Peter Eisentraut έγραψε στις Mar 20, 2006 :
> Eugene E. wrote:
> > the bytea does not output NULs at all.
> > don't mock me.
>
> peter=# create table test (a bytea);
> CREATE TABLE
> peter=# insert into test values ('a\\000b');
> INSERT 0 1
> peter=# select * from test;
>a
>
> a\000b
Just did
dynacom=# SELECT '\150\145\154\154\157'::text;
text
---
hello
(1 row)
dynacom=#
dynacom=#
dynacom=# SELECT '\000\150\145\154\154\157'::text;
text
--
(1 row)
dynacom=#
Oops!
>
>
--
-Achilleus
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
Achilleus Mantzios wrote: > dynacom=# SELECT '\000\150\145\154\154\157'::text; > text > -- > > (1 row) > > dynacom=# > > Oops! text is not bytea. alvherre=# SELECT $$\000\150\145\154\154\157$$::bytea; bytea --- \000hello (1 fila) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] have you feel anything when you read this ?
Peter Eisentraut wrote:
Eugene E. wrote:
the bytea does not output NULs at all.
don't mock me.
peter=# create table test (a bytea);
CREATE TABLE
peter=# insert into test values ('a\\000b');
INSERT 0 1
peter=# select * from test;
a
a\000b
are you kidding ?
where is NUL-byte in "a\000b" ???
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
O Alvaro Herrera έγραψε στις Mar 20, 2006 : > Achilleus Mantzios wrote: > > > dynacom=# SELECT '\000\150\145\154\154\157'::text; > > text > > -- > > > > (1 row) > > > > dynacom=# > > > > Oops! > > text is not bytea. > > alvherre=# SELECT $$\000\150\145\154\154\157$$::bytea; >bytea > --- > \000hello > (1 fila) Sure, but we are trying to reproduce the mysql phaenomenon right? :) > > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Achilleus Mantzios wrote: > O Alvaro Herrera ?? Mar 20, 2006 : > > > text is not bytea. > > > > alvherre=# SELECT $$\000\150\145\154\154\157$$::bytea; > >bytea > > --- > > \000hello > > (1 fila) > > Sure, but we are trying to reproduce the mysql phaenomenon right? :) I don't really know what we are doing in this thread. This Eugene E. seems to be only trolling and the rest of us are feeding him. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
O Eugene E. έγραψε στις Mar 20, 2006 :
> Peter Eisentraut wrote:
> > Eugene E. wrote:
> >
> >>the bytea does not output NULs at all.
> >>don't mock me.
> >
> >
> > peter=# create table test (a bytea);
> > CREATE TABLE
> > peter=# insert into test values ('a\\000b');
> > INSERT 0 1
> > peter=# select * from test;
> >a
> >
> > a\000b
>
> are you kidding ?
> where is NUL-byte in "a\000b" ???
Null byte is a byte of value zero,
and allow me to say that the \000 in "a\000b" is exactly this.
A NULL value is commonly used in C to terminate a pointer's data.
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
--
-Achilleus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
Alvaro Herrera wrote: Achilleus Mantzios wrote: dynacom=# SELECT '\000\150\145\154\154\157'::text; text -- (1 row) dynacom=# Oops! text is not bytea. source says: typedef text varlena; typedef bytea varlena; :-) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > Alvaro Herrera wrote: > >text is not bytea. > > source says: > > typedef text varlena; > typedef bytea varlena; This means that as far as the C type system is concerned, both bytea and text are treated as "struct varlena". It doesn't mean that they are processed by the same input/output functions, which they aren't. NUL bytes are preserved in bytea, and used as terminators in text. I don't find this surprising at all, do you? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] have you feel anything when you read this ?
Achilleus Mantzios wrote:
a
a\000b
are you kidding ?
where is NUL-byte in "a\000b" ???
Null byte is a byte of value zero,
and allow me to say that the \000 in "a\000b" is exactly this.
if ("\0"=="\\000")
printf("congratulations!!!");
NOTE:
I am not care about a _display_ NUL-byte on a screen !
weither it'll be "space" or "some escape sequence"
the problem is: you'll get this four byte sequence '\000' _instead_ of
NUL-byte anyway.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Alvaro Herrera wrote: Eugene E. wrote: Alvaro Herrera wrote: text is not bytea. source says: typedef text varlena; typedef bytea varlena; This means that as far as the C type system is concerned, both bytea and text are treated as "struct varlena". It doesn't mean that they are processed by the same input/output functions, which they aren't. NUL bytes are preserved in bytea, and used as terminators in text. I don't find this surprising at all, do you? I do found surprising it. since both (text and bytea) I/O functions has CSTRING arguments and resut type. - this only means a user should perform some unescaping on the bytea value he got. THE SAME THING he should do with a string value if he decide to use type text and to escape NUL-bytes before input. then what a difference bitween those types except strlen() ? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > the problem is: you'll get this four byte sequence '\000' _instead_ > of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > input. then what a difference bitween those types except strlen() ? bytea does not consider character set encodings and locales, and it handles null bytes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
the problem is: you'll get this four byte sequence '\000' _instead_ of
NUL-byte anyway.
http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html says :
"A binary string is a sequence of octets (or bytes). Binary strings are
distinguished from character strings by two characteristics: First, binary
strings specifically allow storing octets of value zero and other
"non-printable" octets (usually, octets outside the range 32 to 126).
Character strings disallow zero octets, and also disallow any other octet
values and sequences of octet values that are invalid according to the
database's selected character set encoding. Second, operations on binary
strings process the actual bytes, whereas the processing of character
strings depends on locale settings. In short, binary strings are
appropriate for storing data that the programmer thinks of as "raw bytes",
whereas character strings are appropriate for storing text."
That's the whole point of escaping, so that data generated by COPY, for
instance, does not include any funky characters, including the \0 (zero)
character, so that you can use any standard tool on it, including grep...
I LIKE the fact that TEXT refuses invalid characters. It helps find bugs
in my applications, like when I forget to process some 8-bit string before
inserting it in my database which uses UNICODE. I definitely prefer an
error than finding a month later that half my data has had all its
accented characters screwed up.
in psql, you have to use the escaped syntax :
SELECT length('\\000'::BYTEA), length('\\001'::BYTEA),
length('\\000'::TEXT), length('\\001'::TEXT);
length | length | length | length
+++
1 | 1 | 4 | 4
Your client library should take care of escaping and de-escaping. Here, in
python :
cursor.execute( "SELECT %s,%s::BYTEA", ("this is a normal string",
psycopg2.Binary( ">\x00< this is a string with a zero byte" ),) )
r = cursor.fetchone()
print r
['this is a normal string', offset 0 at 0x2ce27c70>]
print str(r[1])
< this is a string with a zero byte
print repr(str(r[1]))
'>\x00< this is a string with a zero byte'
ord(r[1][1])
0
Note : \x00 is python's escaping for the null byte
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] update before drop causes OID problems in transaction?
Jeff Frost wrote: On Sat, 18 Mar 2006, Tom Lane wrote: IIRC you'd have to drop the underlying plpgsql function, not only the trigger object that connects the function to a table. We cache stuff with respect to the function. Tom, sorry it took me a little while to make a test case. The test case is attached. If the attachments don't get through to the mailing list, you can grab the files here: http://www.frostconsultingllc.com/testcase/transaction-test-case-setup.sql http://www.frostconsultingllc.com/testcase/transaction-test-case.sql transaction-test-case-setup.sql will create the appropriate tables and transaction-test-case.sql will demonstrate the error. You can reproduce the problem like so: createdb testcase createlang plpgsql testcase psql -f doc/perpetual/transaction-test-case-setup.sql testcase psql -f doc/perpetual/transaction-test-case.sql testcase psql:transaction-test-case.sql:10: ERROR: could not open relation with OID 2038878 I stripped the tables and queries down to the minimum that demonstrated the error. Interestingly, the problem was not reproducible until I added the credit_card_audit_account_id constraint below: CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id) REFERENCES accounts_basics (id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED I'm not sure a deferred constraint makes sense if you're dropping the table before the end of the transaction. I'm not sure whether the DROP should be prevented or what other error should be provided, but I can't see how both the constraint and the drop can occur. Now that I've got a test case for you guys to look at, I'm off to rewrite our standard procedure to use TRUNCATE instead of DROP. Another problem might well be with your plpgsql trigger function. If you're dropping/re-creating credit_card_audit then that'll give you the error you're seeing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
I wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. You wrote: Your client library should take care of escaping and de-escaping. We both agree as you see. Then i am asking: WHY should a client take care of de-escaping ? Why not to get his data unchanged ? If i forced to de-escape when using bytea then i may use text with the same escaping/de-escaping AS WELL. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] pgsql2shp - Could not create dbf file
Hi everyone: I got an error when I use pgsal2shp. My server is Linux The command I used is: pgsql2shp -f newroads gisdb testarea Where, newroads is my out put file name, gisdb is database name, and testarea is table name. After hit enter button, I got the message: Initializing... Could not create dbf file What was wrong? Any help will be appreciated. Fay
Re: [SQL] have you feel anything when you read this ?
On Mon, 2006-03-20 at 17:53 +0300, Eugene E. wrote: > I wrote: > > >> the problem is: you'll get this four byte sequence '\000' _instead_ > >> of NUL-byte anyway. > > You wrote: > > > Your client library should take care of escaping and de-escaping. > > We both agree as you see. > > Then i am asking: > WHY should a client take care of de-escaping ? Why not to get his data > unchanged ? Request the data be delivered to you in binary format instead of text format (see PQexecParams, PQexecPrepared, etc). When you request text format it requires some escaping to occur for obvious reasons, but you don't need to worry about the length of the data. When you request binary escaping is not required but you do need to worry about the length of the data. Pick the method that suits you best. -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
On Mon, 2006-03-20 at 02:06, Eugene E. wrote: > http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html > --- cut --- > mysql no longer terminates data value display when it encounters a NUL > byte. Instead, it displays NUL bytes as spaces. (Bug #16859) > --- cut --- Everyone here realizes that this is a mysql CLIENT bug, not server side. i.e. if you're writing an application and request that binary text string, you'll get it with nuls in it, just like you put in. Now, I'm not sure that turning nulls into spaces is the best way to handle this in the client. In fact, I'm sure it's not. But this is not a server bug, it's a client bug. Just FYI. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
I wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. You wrote: Your client library should take care of escaping and de-escaping. We both agree as you see. Then i am asking: WHY should a client take care of de-escaping ? Why not to get his data unchanged ? I can understand why you say that for something as simple as a BYTEA, but if the value to be passed to the client is an ARRAY of geometric types or something, you gonna need an open, platform-agnostic exchange format between the way postgres internally represents it and the way the client represents it (in my case, a python list containing instances of python classes representing boxes, etc, it'll be different for every language). Exporting data from postgres in binary is only useful to C programmers who can import the required struct definitions, and you still have to manage the format, it's just that you walk struct's instead of unescaping \'s ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] update before drop causes OID problems in transaction?
On Mon, 20 Mar 2006, Richard Huxton wrote: I stripped the tables and queries down to the minimum that demonstrated the error. Interestingly, the problem was not reproducible until I added the credit_card_audit_account_id constraint below: CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id) REFERENCES accounts_basics (id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED I'm not sure a deferred constraint makes sense if you're dropping the table before the end of the transaction. I'm not sure whether the DROP should be prevented or what other error should be provided, but I can't see how both the constraint and the drop can occur. Indeed much of this transaction might not make sense as it is really all done just for schema change and not part of normal operation. And in fact, you're correct that removing the DEFERRABLE property of the constraint allows the transaction to commit, so the workaround for my update as part of the transaction problem would be to set constraints immediate as part of that transaction like so: SET CONSTRAINTS credit_card_audit_account_id_fkey IMMEDIATE; And indeed this does work. Another problem might well be with your plpgsql trigger function. If you're dropping/re-creating credit_card_audit then that'll give you the error you're seeing. The trigger shouldn't be firing at all in this scenario as it is on credit_card and not credit_card_audit. Are you saying that it could cause this sort of problem even though it doesn't fire? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] update before drop causes OID problems in transaction?
Jeff Frost wrote: Another problem might well be with your plpgsql trigger function. If you're dropping/re-creating credit_card_audit then that'll give you the error you're seeing. The trigger shouldn't be firing at all in this scenario as it is on credit_card and not credit_card_audit. Are you saying that it could cause this sort of problem even though it doesn't fire? My mistake - I'd misread the trigger definition and assumed it was attached to the table you were dropping. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] update before drop causes OID problems in transaction?
Jeff Frost wrote: Another problem might well be with your plpgsql trigger function. If you're dropping/re-creating credit_card_audit then that'll give you the error you're seeing. The trigger shouldn't be firing at all in this scenario as it is on credit_card and not credit_card_audit. Are you saying that it could cause this sort of problem even though it doesn't fire? Actually, my last reply isn't quite accurate. Looking at it, you do update "credit_card_audit" from within the trigger. So, it can cause a problem if called, but if it isn't called then it will not. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] have you feel anything when you read this ?
Alvaro Herrera <[EMAIL PROTECTED]> writes: > text is not bytea. Indeed. I wonder whether we shouldn't tweak the SQL string literal parser to reject \000, because AFAICS that isn't going to do anything useful for any datatype, and it leads to what are at best questionable results. (bytea's processing of \000 happens somewhere further downstream, and wouldn't be affected.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Power cut and performance problem
Hi, We had a power cut lastly and it seems that our PostgreSQL database suffers from performance since. For example, a simple query such as "SELECT MIN(a-primary-key-column) FROM a-table" takes quite a very long time; actually I gave up before getting the result. I shutdown and started up the database, and I took at the log file; I don't see any fatal error: LOG: database system was interrupted at 2006-03-20 22:20:22 GMT LOG: checkpoint record is at 10C/14919ED4 LOG: redo record is at 10C/1487E270; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 166159120; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 10C/1487E270 LOG: incomplete startup packet FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: record with zero length at 10C/14CF39F0 LOG: redo done at 10C/14CF39B4 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: incomplete startup packet LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-03-20 22:30:09 GMT LOG: checkpoint record is at 10C/14CF3A34 LOG: redo record is at 10C/14CF3A34; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 166159788; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: incomplete startup packet LOG: autovacuum: processing database "postgres" LOG: incomplete startup packet LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-03-20 22:31:24 GMT LOG: checkpoint record is at 10C/14CF3A78 LOG: redo record is at 10C/14CF3A78; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 166159796; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: autovacuum: processing database "postgres" LOG: incomplete startup packet Where can I check, please? Is it more likely a hardware problem (the machine seems ok, no error detected)? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Power cut and performance problem
I see you're running autovacuum. What's your disk subsytem look like? By chance is it sitting on a RAID 5 that's running in degraded mode right now while it scrubs? On Mon, 20 Mar 2006, Daniel Caune wrote: Hi, We had a power cut lastly and it seems that our PostgreSQL database suffers from performance since. For example, a simple query such as "SELECT MIN(a-primary-key-column) FROM a-table" takes quite a very long time; actually I gave up before getting the result. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Power cut and performance problem
> I see you're running autovacuum. What's your disk subsytem look like? By > chance is it sitting on a RAID 5 that's running in degraded mode right now > while it scrubs? > Yes, that should be the problem. I will check that tomorrow morning with a Linux administrator. Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Power cut and performance problem
On Mon, 20 Mar 2006, Daniel Caune wrote: I see you're running autovacuum. What's your disk subsytem look like? By chance is it sitting on a RAID 5 that's running in degraded mode right now while it scrubs? Yes, that should be the problem. I will check that tomorrow morning with a Linux administrator. Thanks. BTW, I didn't complete my first thought above, which was to ask when you last vacuumed the DB, but then I saw that you were running autovac, so that wasn't likely the problem. BTW, if the problem is actually a raid array that is rebuilding, it should be (hopefullY) fixed by tomorrow morning. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Update counter when row SELECT'd ... ?
I have a simple table: name, url, counter I want to be able to do: SELECT * FROM table ORDER BY counter limit 5; But, I want counter to be incremented by 1 *if* the row is included in that 5 ... so that those 5 basically move to the bottom of the list, and the next 5 come up ... I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is there anything that I *can* do, other then fire back an UPDATE based on the records I've received? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Update counter when row SELECT'd ... ?
> I have a simple table: > > name, url, counter > > I want to be able to do: > > SELECT * FROM table ORDER BY counter limit 5; > > But, I want counter to be incremented by 1 *if* the row is included in > that 5 ... so that those 5 basically move to the bottom of the list, and > the next 5 come up ... > > I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is > there anything that I *can* do, other then fire back an UPDATE based on > the records I've received? > > Thanks ... > You mean that you want to write a SELECT statement that returns the 5 first rows that have the smallest counter, and just after what, you would like to increment their counter, right? I was thinking of using a table function, something like (I didn't test it): CREATE OR REPLACE FUNCTION my_first_url(P_Limit IN int) RETURNS SETOF table AS $$ BEGIN FOR V_Record IN SELECT * FROM table ORDER BY counter LIMIT P_Limit LOOP UPDATE table SET counter = counter + 1 WHERE name = V_Record.name /* AND url = V_Record.url */; -- if needed RETURN V_Record; END LOOP; END; $$ LANGUAGE PLPGSQL; -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Unable to connect To Database...
hi...
I am doin my project in PHP with PostgreSQL as backend...
Both r very new to me
My OS is LINUX
I am trying to connect to the database bt its not getin connected...
By using
"su posgres" command
i hav created user "root" and database "sample"
By using
"psql -U root sample" command
i hav created table "login" and inserted 3 data's...
now through code using PHP i am trying to insert the data but connection is not getin established..
the code to connect to the datadase is :-
require_once "DB.php";
$uname=$_POST['uname'];
$pass=$_POST['pass'];
$username="root";
$password="";
$hostname="localhost";
$dbname="sample";
$db=new DB;
$dsource=$db->factory("pgsql");
$dcon=pg_connect("dbname=sample user=manashi");
$sql="insert into login values('$uname','$pass')";
$rs=$dsource->pg_query($sql);
?>
is there anythin wrong with the code or sum other problem
waiting for the reply...
