Re: [HACKERS] INTERVAL type: SQL92 implementation
(back on list) > As far as I can see, it is the same. My examples come from Cannan and Otten > on SQL92, but I read the spec for SQL99 and I can't see any obvious > change, except that INTERVAL YEAR TO YEAR (and any other X TO X) is no > longer allowed. (I take it you have a copy of SQL99?) 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? > >o We need to figure out how to parse it in gram.y. I looked at it a > >little bit (a couple of hours?) and it was not obvious how to get rid of > >shift/reduce problems. > I don't have any deep knowledge of yacc/bison...yet. Oh, you will... ;) > I feel unhappy about multiplying interval types like that. I would rather > restrict it to interval (as now), intervalym (YEAR TO MONTH) and intervalds > (DAY TO SECOND), with the parameters determining the interval range. But that means (perhaps?) that you can't define a column INTERVAL DAY, since internally everything would accept all values DAY TO SECOND. I know you proposed setting an internal mask, but that would be per-value, not per-column, so it doesn't help. The attribute system may not be much help here either, unless we somehow generalize it (to allow types to keep their own impure storage?). > otherwise we would have 13 new types and would need to make conversion > functions for all of them. SQL99 says that YEAR TO MONTH and DAY TO SECOND > are incompatible; the results of other combinations give the combined > maximum range: DAY TO HOUR + HOUR TO SECOND = DAY TO SECOND, but I don't > see this as being outside the capabilities of the 2 new types I propose. > Is there some reason in the internals why it would be necessary to create all > 13 new types? 3 for YEAR/MONTH, and 10 for DAY/HOUR/MIN/SEC to get all the combinations. If you convert to a "super interval" for internal operations, then you may only need the I/O and conversion functions, which would be easy. My example still holds as a test case to evaluate an implementation afaik: create table t (id interval day); insert into t(id) select interval '2' day + interval '05' minute; will need to be stored with only the day field non-zero. Certainly that column can not be allowed to end up holding quantities other than integral days, right? Also, the column defined above has no ability to enforce the "day only" character of the column if we are using only a single type and without help from the type or attribute system already in place. > As I said above, I feel that this is to over-complicate things... Hmm, but it may be a required minimum level of complication to meet the spec. Given the arcane syntax and limited functionality (note the gratuitous editorializing ;) it probably isn't worth doing unless it gets us on an obvious path to SQL99-compliant functionality. Also, it is one of the edge cases for SQL99, so even if it is a pain to do we are only doing it once. They couldn't possibly come up with anything uglier for SQL0x, could they? Please say no... ... > the distinction between YEAR TO MONTH and DAY TO SECOND is one that is > present in the existing interval type, so perhaps we could even get away with > only one new type? 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? - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Why "ERROR: dtoi4: integer out of range" on pg_dump
Folks, We have a database with several very large tables. When trying to pg_dump we get the above error, e.g.: pg_dump -v wsdb -- saving database definition -- last builtin oid is 18539 -- reading user-defined types -- reading user-defined functions -- reading user-defined aggregates -- reading user-defined operators -- reading user-defined tables getTables(): SELECT (for PRIMARY KEY) failed on table v3otgdsrcq. Explanation from backend: ERROR: dtoi4: integer out of range Making another small database (same system, 7.1.2 on Debian/GNU Linux 2.2), gives the same sort of problem: pg_dump -v tmp -- saving database definition -- last builtin oid is 18539 -- reading user-defined types -- reading user-defined functions -- reading user-defined aggregates -- reading user-defined operators -- reading user-defined tables -- reading indices information -- reading table inheritance information -- finding the attribute names and types for each table -- finding the attrs and types for table: 'tmp' -- flagging inherited attributes in subtables -- dumping out database comment DumpComment: SELECT failed: 'ERROR: dtoi4: integer out of range If I init a new db and restart postgres with the new base, no problem. I suspect some sort of corruption but we're not sure where to look. A vacuum did not help. We'd like to recover, if at all possible. Any ideas (no luck on other lists or I wouldn't post here)? TIA, --Martin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Bad behaviour when inserting unspecified variable length datatypes
It took me a while to figure out what was going on, but I think I've figured it out. Lets say you have your own variable length datatype called 'MY_DATATYPE'. 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. 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 '-'). This is really bad because a "SELECT * FROM test_table" will send this weird structure to MY_DATATYPE's OUTPUT function. Since this weird structure isn't really a MY_DATATYPE structure, it causes problems. This happens even if you explictly set MY_DATATYPE's DEFAULT to NULL. dave ---(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] Escaping strings for inclusion into SQL queries
Barry Lind wrote: > > I agree with Hannu, that: > > * make SQL changes to allow PREPARE/EXECUTE in main session, not only > in SPI A more ambitious project would be * develop an ANSI standard SQL/CLI compatible postgreSQL client library, change wire protocol and SQL language as needed ;) > is an important feature to expose out to the client. My primary reason > is a perfomance one. Allowing the client to parse a SQL statement once > and then supplying bind values for arguments and executing it multiple > times can save a significant amount of server CPU, since the parsing and > planning of the statement is only done once, even though multiple > executions occur. This functionality is available in the backend > (through SPI) and plpgsql uses it, but there isn't anyway to take > advantage of this SPI functionality on the client (i.e. jdbc, odbc, etc.) > > I could see this implemented in different ways. One, by adding new SQL > commands to bind or execute an already open statement, or two, by > changing the FE/BE protocol to allow the client to open, parse, > describe, bind, execute and close a statement as separate actions that > can be sent to the server in one or more requests. (The latter is how > Oracle does it). The latter is also the ODBS and JDBC wiew of how it is done. The current PG drivers have to fake it all on client side. > > I also would like to see this added to the todo list. > Hannu ---(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] Escaping strings for inclusion into SQL queries
I agree with Hannu, that: * make SQL changes to allow PREPARE/EXECUTE in main session, not only in SPI is an important feature to expose out to the client. My primary reason is a perfomance one. Allowing the client to parse a SQL statement once and then supplying bind values for arguments and executing it multiple times can save a significant amount of server CPU, since the parsing and planning of the statement is only done once, even though multiple executions occur. This functionality is available in the backend (through SPI) and plpgsql uses it, but there isn't anyway to take advantage of this SPI functionality on the client (i.e. jdbc, odbc, etc.) I could see this implemented in different ways. One, by adding new SQL commands to bind or execute an already open statement, or two, by changing the FE/BE protocol to allow the client to open, parse, describe, bind, execute and close a statement as separate actions that can be sent to the server in one or more requests. (The latter is how Oracle does it). I also would like to see this added to the todo list. thanks, --Barry Hannu Krosing wrote: > Bruce Momjian wrote: > >>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. >> >> >>>It has come to our attention that many applications which use libpq >>>are vulnerable to code insertion attacks in strings and identifiers >>>passed to these applications. We have collected some evidence which >>>suggests that this is related to the fact that libpq does not provide >>>a function to escape strings and identifiers properly. (Both the >>>Oracle and MySQL client libraries include such a function, and the >>>vast majority of applications we examined are not vulnerable to code >>>insertion attacks because they use this function.) >>> > > I think the real difference is what I complained in another mail to this > list - > in postgresql you can't do PREPARE / EXECUTE which could _automatically_ > detect > where string escaping is needed or just eliminate the need for escaping. > In postgreSQL you have to construct all queries yourself by inserting > your > parameters inside your query strings in right places and escaping them > when > needed. That is unless you use an interface like ODBC/JDBS that fakes > the > PREPARE/EXECUTE on the client side and thus does the auto-escaping for > you . > > > I think that this should be added to TODO > > * make portable BINARY representation for frontend-backend protocol by > using > typsend/typreceive functions for binary and typinput typoutput for > ASCII > (as currently typinput==typreceive and typoutput==typsend is suspect > the > usage to be inconsistent). > > * make SQL changes to allow PREPARE/EXECUTE in main session, not only in > SPI > > * make changes to client libraries to support marshalling arguments to > EXECUTE > using BINARY wire protocol or correctly escaped ASCII. The binary > protocol > would be very helpful for BYTEA and other big binary types. > > > >>>We therefore suggest that a string escaping function is included in a >>>future version of PostgreSQL and libpq. A sample implementation is >>>provided below, along with documentation. >>> > > While you are at it you could also supply a standard query delimiter > function > as this is also a thing that seems to vary from db to db. > > -- > Hannu > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] [GENERAL] getting the oid for a new tuple in a BEFORE trigger
> we need to control database changes within BEFORE triggers. > There is no problem with triggers called by update, but there is > a problem with triggers called by insert. > > We strongly need to know the oid of a newly inserted tuple. > In this case, we use tg_newtuple of the TriggerData structure > passed to thetrigger function, and its t_data -> t_oid will > have the value '0'. > > Using BEFORE and AFTER triggers would make our lives much harder. > > Is there any way (even hack) to get the oid the newly > inserted tuple will receive? Just set t_data->t_oid = newoid() - this is what backend does in heapam.c:heap_insert(). Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [OT] Re: [HACKERS] User locks code
Serguei Mokhov wrote: > > and why the PostgreSQL project originally is being > released under the BSD-like license? Just curious... Berkeley usually releases their free projects under BSD licence ;) There have been some discussion about changing it, but it has never got enough support. -- Hannu ---(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] Multiple semicolon separated statements and autocommit
On 30 Aug 2001 21:35:42 -0400, you wrote: >"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: >> Are you sure? I thought all that autocommit meant was that a statement that >> is not enclosed within a begin/commit is automatically committed after it is >> run. So, in the this case all three queries will be independent, unless the >> first statements is a 'begin;' and the last is a 'commit;'... > >What does the JDBC spec say about autocommit and ExecuteBatch()? Not much, but that's a different story. We're still in the process of figuring out how to implement this feature exactly. That discussion is on the pgsql-jdbc list. Regards, René Pijlman <[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])
Re: [HACKERS] Multiple semicolon separated statements and autocommit
Christopher Kings-Lynne writes: > Are you sure? Yes. > I thought all that autocommit meant was that a statement that > is not enclosed within a begin/commit is automatically committed after it is > run. So, in the this case all three queries will be independent, unless the > first statements is a 'begin;' and the last is a 'commit;'... Not if they're sent in the same query string. -- 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
