Re: [HACKERS] Porting to Native WindowsNT/2000
Ian Lance Taylor [EMAIL PROTECTED] wrote: Dwayne Miller [EMAIL PROTECTED] writes: Well, for one I have no idea what cygwin is, or what it does to your system, or what security vulnerabilities it might add to your system. It comes with alot of stuff that I may or may not need, but what components I need to run Postgres is not clear. Cygwin is a Unix environment for Windows. For information, see http://cygwin.com/ Cygwin comes with a lot of stuff which you don't need to run Postgres. Simply having that stuff on your computer will not introduce any security vulnerabilities if you don't run the programs. Cygwin is simply a DLL and a bunch of Unix programs. It has no server component. In order to build Postgres, you will need the compiler and associated tools. In order to run all the Postgres commands, you will need the shell and several of the tools. In fact, I believe that a cygwin distribution actually comes with Postgres prebuilt and ready to run. Yes, if you use the setup.exe at cygwin.com, it will by default include postgres. It would be nice if we had a minimal list of programs need to run Postgresql (To be honest, the idea of worrying about security vulnerabilities on Windows seems odd to me. If you are honestly worried about security on your database server, the first step is to stop running Windows.) That's just a cheap shot. I've seen no evidence that Windows NT/2000 is inherently less secure than any given Unix or Linux distribution, it is just a lot more popular and tends to have less experienced system administrators. Having an easy-to-install Windows set up would be a plus for Postgres. There are millions of Windows NT servers out there. Two could Postgres be made more efficient on Windows if it ran without cygwin? Yes. Cygwin adds measurable overhead to all I/O operations, and obviously a database does a lot of I/O. Postgres employs operations which are fast on Unix but are very slow on cygwin, such as fork. As mlw said, porting Postgres to run natively on Windows would be a significant effort. The forking mechanism it uses currently would have to be completely rearchitected. This is true. However, a process-pool architecture would benefit Postgres on other platforms besides Windows. Postgresql has been ported to the HP3000 MPE/iX operating system, for example, which is POSIX-compliant, but has an awfully slow fork(). The buffer, file manager, and networking code would have to be rewritten. I don't think this is true. Most of the unix-style interfaces are supported out of the box by the Microsoft C compiler. Three can you start cygwin programs on startup of the system? Sure. cygwin programs are just Windows programs which use a particular DLL. It's not quite as simple as that. You can run it as a service under the SRVANY program, but that doesn't provide for a clean shut-down. Has anybody written an NT service wrapper for Postgresql? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Need help in composing PostgreSQL query
Hi
Please help me compose the query in PostgreSQL.
Using PostgreSQL 7.1.2.
Suppose relations A and B have columns:
{X1, X2, ..., Xm, Y1, Y2, ..., Yn}
and
{Y1, Y2, ..., Yn}
Attributes Y1, Y2, ..., Yn are common for both relations
and have the same type in both.
How can I define in PostgreSQL the query producing
relation with columns X1,X2,...,Xm containing all those tuples
satisfying conditon: relation A contains tupple
{x1,x2,...xm,y1,y2,...,yn}
for _each_ tupple
{y1,y2,...,yn}
in relation B ? Where x1 denotes particular value of
colum X1 etc.
For example: consider two tables DEND and DOR.
DEND DOR
s | p p
+
s1 | p1 p1
s1 | p2 p2
s1 | p3 p3
s1 | p4 p4
s1 | p5 p5
s1 | p6 p5
s2 | p1 (6 rows)
s2 | p2
s3 | p2
s4 | p2
s4 | p4
s4 | p5
(12 rows)
For such tables our desired query should return:
s
s1
Thanks in advance.
--
Vladimir Zolotych [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Conditional operators ALL, ANY in WHERE clause
Hi The following is the quote describing WHERE clause of SELECT (pgsql/doc/html/sql-select.html). WHERE Clause The optional WHERE condition has the general form: WHERE boolean_expr boolean_expr can consist of any expression which evaluates to a boolean value. In many cases, this expression will be: expr cond_op expr or log_op expr where cond_op can be one of: =, , =, , = or , a conditional operator like ALL, ANY, IN, LIKE, or a locally defined operator, and log_op can be one of: AND, OR, NOT. SELECT will ignore all rows for which the WHERE condition does not return TRUE. Please give me hints how can I use conditional operators ALL, ANY in WHERE clause. Some examples will be appreciated. Thanks in advance. -- Vladimir Zolotych [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Porting to Native WindowsNT/2000
Dwayne Miller [EMAIL PROTECTED] writes: Well, for one I have no idea what cygwin is, or what it does to your system, or what security vulnerabilities it might add to your system. It comes with alot of stuff that I may or may not need, but what components I need to run Postgres is not clear. Cygwin is a Unix environment for Windows. For information, see http://cygwin.com/ Cygwin comes with a lot of stuff which you don't need to run Postgres. Simply having that stuff on your computer will not introduce any security vulnerabilities if you don't run the programs. Cygwin is simply a DLL and a bunch of Unix programs. It has no server component. In order to build Postgres, you will need the compiler and associated tools. In order to run all the Postgres commands, you will need the shell and several of the tools. In fact, I believe that a cygwin distribution actually comes with Postgres prebuilt and ready to run. (To be honest, the idea of worrying about security vulnerabilities on Windows seems odd to me. If you are honestly worried about security on your database server, the first step is to stop running Windows.) Two could Postgres be made more efficient on Windows if it ran without cygwin? Yes. Cygwin adds measurable overhead to all I/O operations, and obviously a database does a lot of I/O. Postgres employs operations which are fast on Unix but are very slow on cygwin, such as fork. As mlw said, porting Postgres to run natively on Windows would be a significant effort. The forking mechanism it uses currently would have to be completely rearchitected. The buffer, file manager, and networking code would have to be rewritten. Off the top of my head, for a top programmer who is an expert in Unix, Windows, and Postgres, it might take a year. There would also be a heavy ongoing maintenance cost to keep up with new Postgres releases. Three can you start cygwin programs on startup of the system? Sure. cygwin programs are just Windows programs which use a particular DLL. Ian Cygrunsrv allows postgresql to be run as a service. There's a slight hiccup on shutdown meaning that the postmaster.pid file gets left. This is due to sighup being sent by windows shutdown. I think current cygwin snapshots might cure this, otherwise there is a patch some where that causes SIGHUP to be ignored. I *think* the pre-built binary already has this patch applied. - Stuart ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] INDEX BUG???
hello All I tried the following commands: ponto=# explain select * from horarios where funcionario1; NOTICE: QUERY PLAN: Seq Scan on horarios (cost=0.00..176.21 rows=2432 width=132) EXPLAIN ponto=# explain select * from horarios where funcionario=1; NOTICE: QUERY PLAN: Index Scan using horarios_func_data on horarios (cost=0.00..55.37 rows=73 width=132) EXPLAIN So my question is why in the first case the postgre did'nt use the index and made a seq scan ?? thanks and sorry about my english... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Porting to Native WindowsNT/2000
Ken Hirsch [EMAIL PROTECTED] writes: (To be honest, the idea of worrying about security vulnerabilities on Windows seems odd to me. If you are honestly worried about security on your database server, the first step is to stop running Windows.) That's just a cheap shot. I've seen no evidence that Windows NT/2000 is inherently less secure than any given Unix or Linux distribution, it is just a lot more popular and tends to have less experienced system administrators. I agree that it looks like a cheap shot, but I didn't intend to make one. There are various arguments why Windows NT is probably less secure than Unix, ranging from interface design to code maturity to platform popularity to actual statistics of numbers of cracked systems and numbers of different successful cracks. I personally don't know of any arguments why Unix is less secure than Windows NT, other than guessing. Unless you are an expert in the field, which I am not, I think you should follow the preponderance of evidence, which I read as saying that where security is a significant concern, it's best to avoid Windows. (This is off-topic for the Postgres mailing list, though, so if I reply on this further I'll take it off list.) Having an easy-to-install Windows set up would be a plus for Postgres. There are millions of Windows NT servers out there. I agree. Two could Postgres be made more efficient on Windows if it ran without cygwin? Yes. Cygwin adds measurable overhead to all I/O operations, and obviously a database does a lot of I/O. Postgres employs operations which are fast on Unix but are very slow on cygwin, such as fork. As mlw said, porting Postgres to run natively on Windows would be a significant effort. The forking mechanism it uses currently would have to be completely rearchitected. This is true. However, a process-pool architecture would benefit Postgres on other platforms besides Windows. Postgresql has been ported to the HP3000 MPE/iX operating system, for example, which is POSIX-compliant, but has an awfully slow fork(). On the other hand, POSIX-compliant systems generally are moving toward a faster and faster fork, as they should given the nature of POSIX programs. A process pool architecture for a system like Postgres would require very careful attention to memory usage, in order to be able to return swap space to the system or at least avoid using it. Otherwise, I believe the different processes would fragment memory over time, decreasing system performance. Process pools work best for systems with fixed memory usage. The buffer, file manager, and networking code would have to be rewritten. I don't think this is true. Most of the unix-style interfaces are supported out of the box by the Microsoft C compiler. I've written code which ran natively on both Unix and Windows, and that kind of statement doesn't get you very far. Even when the interfaces are the same, there are critical differences all over the place (e.g., select() on Windows only works on sockets, not pipes). You can deal with each problem as it comes up, but they keep coming up. That's why Steve Chamberlain started the cygwin project in the first place--we were both at Cygnus at the time, and I spent several months working on cygwin myself a couple of years later. Ian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] INTERVAL type: SQL92 implementation
I seem to have the complete released (I think) SQL99 docs. If anyone wants them - just reply to me personally. Should they be put on the postgres site? Is that legal? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ken Hirsch Sent: Sunday, 2 September 2001 5:18 AM To: Hackers List Subject: Re: [HACKERS] INTERVAL type: SQL92 implementation Thomas Lockhart [EMAIL PROTECTED] wrote: We have a copy of an SQL99 draft which seems to be reasonably complete. afaik we haven't come across an actual released version. Let me know if you want me to forward it; perhaps it is on the ftp or web site? ftp://ftp.postgresql.org/pub/doc/sql/sql1998.tar.gz Mostly the same files are at http://gatekeeper.research.compaq.com/pub/standards/sql/ (or ftp). I didn't know until recently that the ANSI standard was available in PDF form for an almost reasonable price ($18/part) compared to the outrageous ISO price ($98 to $275 per part). See http://webstore.ansi.org/ansidocstore/find.asp?find_spec=sql [...] Not sure what you mean here. The existing type does keep years/months stored separately from the days/hours/minutes/seconds (a total of two internal fields) but SQL99 asks that these be kept completely away from each other from what you've said. Does it define any arithmetic between the two kinds of intervals? No. Days/hours/minutes/seconds are exact quantities whereas years and months are not, so they don't mix. ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] INDEX BUG???
gabriel writes: So my question is why in the first case the postgre did'nt use the index and made a seq scan ?? Because it thinks the sequential scan will be faster. You didn't show any evidence to the contrary. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] INDEX BUG???
hello All I tried the following commands: ponto=# explain select * from horarios where funcionario1; NOTICE: QUERY PLAN: Seq Scan on horarios (cost=0.00..176.21 rows=2432 width=132) EXPLAIN ponto=# explain select * from horarios where funcionario=1; NOTICE: QUERY PLAN: Index Scan using horarios_func_data on horarios (cost=0.00..55.37 rows=73 width=132) EXPLAIN So my question is why in the first case the postgre did'nt use the index and made a seq scan ?? In the first case it estimates 2432 rows returned, in the second it estimates 73 rows. How big is the table in question? Have you vacuum analyzed recently? Are those reasonable estimates? (ie, what would a select count(*) show for those two conditions) At some point, the cost of doing the index scan exceeds that of the seq scan because the index scan requires reading the heap file in random order so that we know if the tuple is visible to the selecting transaction (in addition to the reading of the index itself). If it's choosing the wrong plan that usually means the estimates are off. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Escaping strings for inclusion into SQL queries
Peter Eisentraut [EMAIL PROTECTED] writes:
Florian Weimer writes:
The first version escaped ' with ''. I changed it when I noticed that
if \' is used instead, the same function can be used for strings
('...') and identifiers (...).
Last time I checked (15 seconds ago), you could not escape with \ in
PostgreSQL. The identifer parsing rules are a bit different from strings.
Yes, we misread the lexer description. I'm sorry about that.
In addition, there seems to be a bug in the treatment of escapes in
identifiers. 'SELECT ;' yields the error message 'Attribute ''
not found ' (not ''!) or even 'Attribute '\' not found', depending
on the queries executed before.
For identifiers, comparing the characters to a white list is probably
a more reasonable approach.
--
Florian Weimer[EMAIL PROTECTED]
University of Stuttgart http://cert.uni-stuttgart.de/
RUS-CERT +49-711-685-5973/fax +49-711-685-5898
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Escaping strings for inclusion into SQL queries
Bruce Momjian [EMAIL PROTECTED] writes: OK, can you supply an updated patch? Yes, I'm going to update it. Shall I post it here? Sure, or patches list. Could anybody have a look at the parser issue? I am unsure how it is supposed to behave. Comments? Does the standard say anything? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Porting to Native WindowsNT/2000
Ian Lance Taylor ( others) wrote: This is true. However, a process-pool architecture would benefit Postgres on other platforms besides Windows. Postgresql has been ported to the HP3000 MPE/iX operating system, for example, which is POSIX-compliant, but has an awfully slow fork(). On the other hand, POSIX-compliant systems generally are moving toward a faster and faster fork, as they should given the nature of POSIX programs. A process pool architecture for a system like Postgres would require very careful attention to memory usage, in order to be able to return swap space to the system or at least avoid using it. Otherwise, I believe the different processes would fragment memory over time, decreasing system performance. Process pools work best for systems with fixed memory usage. What about a pre-forked model? What about using the Apache Portable Runtime? The Apache Postgres licenses are compatible, are they not? Cheers, Colin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bad behaviour when inserting unspecified variable length datatypes
Dave Blasby [EMAIL PROTECTED] writes: CREATE TABLE test_table (myint integer, mydata MY_DATATYPE); INSERT INTO test_table VALUES (1); At this point, I'd expect there to be one row in test table. The myint column will have the value one, and the mydata column will have the value NULL. Check... This doesnt appear to be the case. It seems that the mydata column will have a structure that looks like a '-'::TEXT structure (ie. the first 4 bytes are an int representing 5, and the 5th byte is the ASCII '-'). Uh, what did your CREATE TYPE command look like, exactly? This sounds like you specified a default value for the datatype. Maybe you need to show us your datatype's I/O functions, too. Since this works perfectly fine for the standard variable-length datatypes, it's hard to arrive at any other conclusion than that your custom datatype code is erroneous. But there's not enough info here to figure out just what is wrong with it. 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] Re: Escaping strings for inclusion into SQL queries
OK, can you supply an updated patch?
Peter Eisentraut [EMAIL PROTECTED] writes:
Florian Weimer writes:
The first version escaped ' with ''. I changed it when I noticed that
if \' is used instead, the same function can be used for strings
('...') and identifiers (...).
Last time I checked (15 seconds ago), you could not escape with \ in
PostgreSQL. The identifer parsing rules are a bit different from strings.
Yes, we misread the lexer description. I'm sorry about that.
In addition, there seems to be a bug in the treatment of escapes in
identifiers. 'SELECT ;' yields the error message 'Attribute ''
not found ' (not ''!) or even 'Attribute '\' not found', depending
on the queries executed before.
For identifiers, comparing the characters to a white list is probably
a more reasonable approach.
--
Florian Weimer [EMAIL PROTECTED]
University of Stuttgart http://cert.uni-stuttgart.de/
RUS-CERT +49-711-685-5973/fax +49-711-685-5898
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(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] [PATCH] Win32 errno a little bit safer
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. Hello, i just reviewed the win32 errno patch and i saw that maybe i didn't really played it totally safe in my last suggestion, the system table might pick up the msg but not the netmsg.dll, so better try both. I also added a hex printout of the errno appended to all messages, that's nicer. If anyone hate my coding style, or that i'm using goto constructs, just tell me, and i'll rework it into a nested if () thing. Patch attached. Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [ Attachment, skipping... ] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Why ERROR: dtoi4: integer out of range on pg_dump
Martin Weinberg [EMAIL PROTECTED] writes: DumpComment: SELECT failed: 'ERROR: dtoi4: integer out of range Hmm. I can reproduce this error message if I suppose that you have OIDs exceeding 2 billion. pg_dump will produce queries like: regression=# select * from pg_description where objoid = 25; ERROR: dtoi4: integer out of range A short-term workaround is to hack pg_dump so that it explicitly coerces the literal to OID and/or quotes the literal: regression=# select * from pg_description where objoid = 25::oid; objoid | classoid | objsubid | description +--+--+- (0 rows) regression=# select * from pg_description where objoid = '25'; objoid | classoid | objsubid | description +--+--+- (0 rows) This is done in many places in pg_dump, but not in DumpComment which is relatively new code :-( A longer-term question is how to persuade the parser to get this right without such help. I think that this is another variant of the perennial numeric-precision issue and will not be real easy to fix. 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] Re: Escaping strings for inclusion into SQL queries
Florian Weimer writes:
In addition, there seems to be a bug in the treatment of escapes in
identifiers. 'SELECT ;' yields the error message 'Attribute ''
not found ' (not ''!) or even 'Attribute '\' not found', depending
on the queries executed before.
A bug indeed.
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/scan.l,v
retrieving revision 1.88
diff -u -r1.88 scan.l
--- scan.l 2001/03/22 17:41:47 1.88
+++ scan.l 2001/09/03 22:11:46
@@ -375,7 +375,7 @@
return IDENT;
}
xd{xddouble} {
- addlit(yytext, yyleng-1);
+ addlit(yytext+1, yyleng-1);
}
xd{xdinside} {
addlit(yytext, yyleng);
===end
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(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] Conditional operators ALL, ANY in WHERE clause
Vladimir V. Zolotych wrote: Please give me hints how can I use conditional operators ALL, ANY in WHERE clause. [This query would have been better directed to the pgsql-sql list.] Some examples will be appreciated. ALL is used to test a value against all of a list of items. Find the customer whose account has been created the longest: SELECT id, date_opened FROM customer WHERE date_opened IS NOT NULL AND date_opened = ALL (SELECT date_opened FROM customer WHERE date_opened IS NOT NULL); id | date_opened ---+- 25832 | 1998-01-05 (1 row) ANY is used to compare against any item of the list; x = ANY y is the same as x IN y: SELECT COUNT(*) FROM customer WHERE area = ANY (SELECT id FROM country); count --- 216 (1 row) But note that use of ALL may be very inefficient: bray=# explain select id,date_opened from customer where date_opened is not null and date_opened = all (select date_opened from customer where date_opened is not null); NOTICE: QUERY PLAN: Seq Scan on customer (cost=0.00..240125.47 rows=1144 width=16) SubPlan - Seq Scan on customer (cost=0.00..139.89 rows=1144 width=4) EXPLAIN bray=# explain select id,date_opened from customer where date_opened is not null and date_opened = (select min(date_opened) from customer); NOTICE: QUERY PLAN: Seq Scan on customer (cost=0.00..148.47 rows=381 width=16) InitPlan - Aggregate (cost=139.89..139.89 rows=1 width=4) - Seq Scan on customer (cost=0.00..131.31 rows=3431 width=4) EXPLAIN -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C And he said unto his disciples, Therefore I say unto you, Take no thought for your life, what ye shall eat; neither for the body, what ye shall put on. For life is more than meat, and the body is more than clothing. Consider the ravens, for they neither sow nor reap; they have neither storehouse nor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how they grow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not arrayed like one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast into the oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or what ye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these things shall be added unto you. Luke 12:22-24; 27-29; 31. ---(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] Re: Toast,bytea, Text -blob all confusing
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: I think actually the backend parser has no business changing constants, he is imho only allowed to parse it, so he knows where a constant begins, and where it ends. How do you propose to handle embedded quote marks in literals, if there is no parser-level escape convention? Don't suggest a type-specific escape convention; at the time the parser runs, it's impossible to know what type the literal will turn out to be. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Escaping strings for inclusion into SQL queries
Peter Eisentraut [EMAIL PROTECTED] writes:
A bug indeed.
xd{xddouble} {
- addlit(yytext, yyleng-1);
+ addlit(yytext+1, yyleng-1);
}
I don't follow. xddouble can only expand to two quote marks, so how
does it matter which one we use as the result? This seems unlikely
to change the behavior. If it does, I think the real bug is elsewhere.
I do see a bug here --- I get
regression=# select ;
NOTICE: identifier [ lots o' rubouts ] @;Ç will
be truncated to
ERROR: Attribute '' not found
regression=#
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bytea/Base64 encoders for libpq - interested?
Where did we leave this?
On Tue, Aug 28, 2001 at 11:07:32AM +0200, Joerg Hessdoerfer wrote:
Hi!
Please find attached some very simple encoders/decoders for bytea and base64.
Bytea encoder is very picky about what it leaves unescaped - basically the
base64
char set ;-)
Since this seems to be a very poorly documented but much asked-for thing, I
thought
you would maybe like to add this code to libpq (so that everyone benefits).
I'm aware that function renames might be necessary, though.
If you like, I could make the code fit into libpq, and send diffs.
Any comments/interests?
What implement base64 PostgreSQL datetype that use externaly base64 and
internaly same things as bytea. It prevent FE and parser problems with
bad chars and internaly for data storage save less space than text
with base64. Of course it doesn't solve a problem with encoding/decoding
data in your application to/from base64. May be implement for this
datetype cast to/from bytea too.
SELECT my_bytea::base64 FROM foo;
INSERT INTO foo (my_bytea) VALUES ('some_base64_string'::bytea);
And you can still fetch all data directly in batea by binary cursor.
Comments?
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Escaping strings for inclusion into SQL queries
Tom Lane writes:
Peter Eisentraut [EMAIL PROTECTED] writes:
A bug indeed.
xd{xddouble} {
- addlit(yytext, yyleng-1);
+ addlit(yytext+1, yyleng-1);
}
I don't follow. xddouble can only expand to two quote marks, so how
does it matter which one we use as the result?
addlit() expects the first argument to be null-terminated and implicitly
uses that null byte at the end of the supplied argument to terminate its
own buffer. It expects to copy doublequotenull (new version), whereas
it got (old version) doublequotedoublequote and left the buffer
unterminated, which leads to random behavior, as you saw.
Since there are only a few calls to addlit(), I didn't feel like
re-engineering the whole interface to be prettier. It does look like a
performance-beneficial implementation.
A concern related to the matter is that if you actually put such an
identifier into your database you basically make it undumpable (well,
unrestorable) because no place is prepared to handle such a thing.
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(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] Re: Escaping strings for inclusion into SQL queries
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: I don't follow. xddouble can only expand to two quote marks, so how does it matter which one we use as the result? addlit() expects the first argument to be null-terminated and implicitly uses that null byte at the end of the supplied argument to terminate its own buffer. Hmm, so I see: /* append data --- note we assume ytext is null-terminated */ memcpy(literalbuf+literallen, ytext, yleng+1); literallen += yleng; Given that we are passing the length of the desired string, it seems bug-prone for addlit to *also* expect null termination. I'd suggest memcpy(literalbuf+literallen, ytext, yleng); literallen += yleng; literalbuf[literallen] = '\0'; instead. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Bytea/Base64 encoders for libpq - interested?
Bruce Momjian [EMAIL PROTECTED] writes: Where did we leave this? I don't think adding a datatype just to provide base64 encoding is a wise approach. The overhead of a new datatype (in the sense of providing operators/functions for it) will be much more than the benefit. I think providing encode/decode functions is sufficient... and we have those already, don't we? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bytea/Base64 encoders for libpq - interested?
Bruce Momjian [EMAIL PROTECTED] writes: Where did we leave this? I don't think adding a datatype just to provide base64 encoding is a wise approach. The overhead of a new datatype (in the sense of providing operators/functions for it) will be much more than the benefit. I think providing encode/decode functions is sufficient... and we have those already, don't we? Agreed. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Another pgindent request
Already handled. I ran it on proc.c and got: MyProc-errType = STATUS_OK;/* initialize result for success */ The feature was added with: # add space after comments that start on tab stops sed 's,;\(/\*.*\*/\)$,; \1,' | I must have added this since 7.1, probably because of a mention from you. As long as you're hacking pgindent, can you do something about its habit of sometimes removing all space before a same-line comment? Here's an example from the 7.1 run (in src/backend/storage/lmgr/proc.c): *** *** 607,613 MyProc-waitHolder = holder; MyProc-waitLockMode = lockmode; ! MyProc-errType = STATUS_OK; /* initialize result for success */ /* mark that we are waiting for a lock */ waitingForLock = true; --- 612,618 MyProc-waitHolder = holder; MyProc-waitLockMode = lockmode; ! MyProc-errType = STATUS_OK;/* initialize result for success */ /* mark that we are waiting for a lock */ waitingForLock = true; *** IMHO there should always be at least one space before a same-line comment. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: Toast,bytea, Text -blob all confusing
Thanks you your description, I have added a bytea section to the docs. Patch attached. For bytea, follow this rule: to escape a null character, use this: '\\0'. To escape a backslash, use this: ''. Same idea to unescape data. Are there other characters that need to be escaped? I suspect there are more characters that need to be escaped - ctrl chars? single quotes?. Why four backslashes for one? Is there a definitive documentation anywhere for what bytea is _supposed_ (not what it might actually be) to be and how it is to be handled? Yes, sorry for being unclear on this one. Here's a more detailed explanation: Bytea is just a stream of data. On input, it must follow C escaping conventions, on output, it will be escaped using C escaping conventions. However, there's a trap: before things get to bytea input handler, they are first processed by postgresql general parser. Hence, the string \\0 given from psql will become \0 when it gets to bytea input handler. String will become \\. All non-printable characters must be escaped like this: \\(octal of character), for ex, chr(255) must be presented as \\377. (If you want, you can also use this as an alternative and more generic way to escape a backslash, \\134). Single quote must be escaped either as \\47 or as \'. Note the single backslash. Why only one? Because bytea parser doesn't care about single quotes and you only need to escape it for the postgresql parser... So, just keep in mind the double-parsing of input and you'll be safe. Also why wouldn't escaping stuff like this work with TEXT then? If a null is going to be backslash backslash zero, and come out the same way, it sure looks like TEXT to me :). OK so there's this thing about storage. So maybe Because text is null-terminated, can't have a null inside. I could save a byte by just converting nulls to backslash zero and real backslashes to backslash backslash. Tada. If you do that, you'll break ordering/comparison. Bytea in memory is stored EXACTLY the way input string was, without any escaping, hence, all comparisons will be correct ( '\\0'::bytea is less than '\\1'::bytea). With your representation, comparisons will fail, because in memory, data is escaped using some escaping convention that you made up. OK it's probably not the same, but having to put four backslashes when two should be enough to quote one makes me rather puzzled and uneasy. Double parsing, hence double escaping. Great explanation Alex --thanks! I'll add a bit: I've done about 400,000 inserts and subsequent queries to verify that, from PHP at least, only four charaters need to be escaped. The inserts were 20 byte strings gotten by concatenating some known text with a counter in a loop, and then producing a SHA-1 hash. This produces very uniformly distributed binary data. Then I ran the same loop, except I queried for the inserted strings. I'm quite confident from this experiment that binary can reliably be inserted via standard SQL when these four characters are escaped. Here's the run down: \\000First slash is consumed by the general parser, leaving \000 for the byteain function. If you only use one '\', the general parser converts the character into a true '\0' byte, and the byteain function sees this byte as the string terminator and stops. This causes the input string to be truncated (which seems to confuse many people). \\012In my early tests 0x0a (LF) was getting converted to 0x20 (space). I think this was happening during PHP's parsing, but I'm still not sure. I'll dig into this some more later. \\047As Alex mentioned, the byteain function doesn't treat this as a special character, but of course the general parser does as this is a single quote. It also works fine to escape it as \', I just prefer to use all octals. \\134Both the general parser and the byteain function see this (a single \) as the special escape character. Therefore the general parser turns into \\, and the byteain function turns \\ into \ for actual storage. Again, I prefer to use the octal representation instead. I hope this helps reduce the concerns and confusion over bytea. If anyone can help explain why my linefeeds were getting converted to spaces, all the mysteries would be explained ;-) -- Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 Index: doc/src/sgml/datatype.sgml === RCS file:
Re: [HACKERS] Bytea/Base64 encoders for libpq - interested?
I don't think adding a datatype just to provide base64 encoding is a wise approach. The overhead of a new datatype (in the sense of providing operators/functions for it) will be much more than the benefit. I think providing encode/decode functions is sufficient... and we have those already, don't we? It might be nice to have a PQbyteaEscape or some such function available in the libpq client library so that arbitrary binary could be escaped on the client side and used in a sql statement. I actually wrote this already as an addition to the PHP PostgreSQL extension, but it would make more sense, now that I think about it, for it to be in libpq and called from PHP (or whatever). Comments? On a related note, are there any other bytea functions we should have in the backend before freezing for 7.2? I was thinking it would be nice to have a way to cast bytea into text and vice-versa, so that the normal text functions could be used for things like LIKE and concatenation. Any interest in this? If so, any guidance WRT how it should be implemented? -- Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bytea/Base64 encoders for libpq - interested?
I don't think adding a datatype just to provide base64 encoding is a wise approach. The overhead of a new datatype (in the sense of providing operators/functions for it) will be much more than the benefit. I think providing encode/decode functions is sufficient... and we have those already, don't we? It might be nice to have a PQbyteaEscape or some such function available in the libpq client library so that arbitrary binary could be escaped on the client side and used in a sql statement. I actually wrote this already as an addition to the PHP PostgreSQL extension, but it would make more sense, now that I think about it, for it to be in libpq and called from PHP (or whatever). Comments? Good idea. I will commit the non-bytea escape in a day and you can base a bytea one on that. You will have to pass in the length of the field because of course it is not null terminated. On a related note, are there any other bytea functions we should have in the backend before freezing for 7.2? I was thinking it would be nice to have a way to cast bytea into text and vice-versa, so that the normal text functions could be used for things like LIKE and concatenation. Any interest in this? If so, any guidance WRT how it should be implemented? I can't see why you can't do that. The only problem is passing a \0 (null byte) back to the client. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Re: Toast,bytea, Text -blob all confusing
\\012In my early tests 0x0a (LF) was getting converted to 0x20 (space). I think this was happening during PHP's parsing, but I'm still not sure. I'll dig into this some more later. redfaced The script I was using in PHP *explicitly* converted all linefeeds to spaces. Mystery solved. /redfaced I think Bruce's text still works though. -- Joe ---(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] Bytea/Base64 encoders for libpq - interested?
Joe Conway [EMAIL PROTECTED] writes: I was thinking it would be nice to have a way to cast bytea into text and vice-versa, How will you handle a null byte in bytea data? Transforming it directly into an embedded null in a text object is NOT an acceptable answer, because too many of the text functions will misbehave on such data. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Toast,bytea, Text -blob all confusing
Bruce Momjian [EMAIL PROTECTED] writes: However, there's a trap: before things get to bytea input handler, they are first processed by postgresql general parser. This description fails to make clear that the two levels of parsing only apply for datums that are written as string literals in SQL commands. An example where this doesn't apply is COPY input data. 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] Bytea/Base64 encoders for libpq - interested?
It might be nice to have a PQbyteaEscape or some such function available in the libpq client library so that arbitrary binary could be escaped on the client side and used in a sql statement. I actually wrote this already as an addition to the PHP PostgreSQL extension, but it would make more sense, now that I think about it, for it to be in libpq and called from PHP (or whatever). Comments? Good idea. I will commit the non-bytea escape in a day and you can base a bytea one on that. You will have to pass in the length of the field because of course it is not null terminated. OK. On a related note, are there any other bytea functions we should have in the backend before freezing for 7.2? I was thinking it would be nice to have a way to cast bytea into text and vice-versa, so that the normal text functions could be used for things like LIKE and concatenation. Any interest in this? If so, any guidance WRT how it should be implemented? I can't see why you can't do that. The only problem is passing a \0 (null byte) back to the client. Well, ISTM the simplest (if not the most efficient) way to do bytea-to-text would be a function that takes the escaped string value from byteaout, and creates a text value directly from it. The only danger I can think of is that very long strings might need to be truncated in length, since the escaped string could be significantly longer than the binary. Text-to-bytea should be a straight copy, since nothing that can be represented as text cannot be represented as bytea. Any comments or concerns? -- Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Toast,bytea, Text -blob all confusing
Bruce Momjian [EMAIL PROTECTED] writes: However, there's a trap: before things get to bytea input handler, they are first processed by postgresql general parser. This description fails to make clear that the two levels of parsing only apply for datums that are written as string literals in SQL commands. An example where this doesn't apply is COPY input data. Are you talking about my SGML changes? I clearly mention quote-handling and bytea handling, which pretty clearly not apply in COPY. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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
[HACKERS] Fw: Random strings
Below is the last message I sent (to patches) regarding the random string function for contrib. Is there any interest in this? I don't mind changing it per Peter's comments, but I don't want to bother if no one sees any value in it. Comments? -- Joe - Original Message - From: Joe Conway [EMAIL PROTECTED] To: Peter Eisentraut [EMAIL PROTECTED] Cc: Dr. Evil [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, August 09, 2001 10:13 AM Subject: Re: [PATCHES] Random strings seconds). The same test with /dev/urandom returns instantly. Perhaps there should be an option to use either. For instances where only a few truly random bytes is needed (i.e. one session key), use /dev/random. When you need many random bytes quickly, use /dev/urandom? Not sure if this is intuitive. How many bytes is a few? Maybe just be honest about it and name them randomstr and urandomstr or such. In the patch that I sent last night, I explicitly limited /dev/random to 64 bytes. I agree that this is not very intuitive, but for specific purposes, such as generating a session key for tripledes (24 byte/192 bit random string yielding 168 bits for a the key) periodically, it is quite useful. There's a tradeoff here between cryptographic strength (favoring /dev/random) and application performance (favoring /dev/urandom) that will vary significantly from application to application. It's nice to have the option depending on your needs. Having said that, I'm not married to the idea that we should provide access to both /dev/random and /dev/urandom. I'd be happy to roll another patch, limited to just urandom, and renaming the function if you feel strongly about it. (should we move this discussion back to hackers to get a wider audience?) -- Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
