Re: [GENERAL] Why is create function bringing down the Backend server?
On 12/23/05, Carlos Moreno <[EMAIL PROTECTED]> wrote: > Marko Kreen wrote: > > >On 12/22/05, Carlos Moreno <[EMAIL PROTECTED]> wrote: > > > >>The problem is, when I execute the SQL statement: > >> > >>create or replace function sha1 ; > >> > >>for the second time (i.e., after making modifications and > >>recompiling), the *backend* crashes -- it then restarts > >>automatically, and then I run again the create or replace > >>statement, and it works now (and the function seems to > >>work fine -- well, in its "final" version it does). > >> > > > >You should see if there's something in server log. > > > > The only thing that does show does not seem to say much: > > LOG: server process (PID 12885) was terminated by signal 11 > LOG: terminating any other active server processes > LOG: all server processes terminated; reinitializing > > Not sure what the meaning of signal 11 is with PG (AFAIR, it's > one of the SIGUSER values, right?) Well, that does not help. Signal 11 is SIGSEGV. > >And then indeed, try to gdb it. You can run Postgres in non-daemon > >mode with command 'postgres -D datadir database'. > > > >The stripped pgcrypto.c you posted - your wrapper function looks fine, > >only problem I see is that you deleted function find_provider that is used > >by pg_digest, so there will be undefined function in final .so. > > > > Oh no!! That was only in the function I posted, so that the file > is kept as short as possible -- in the one that I compiled, I left > everything untouched, and only added my functions. Ah, that's not it. Then I'm clueless. > >But that should not crash the server, so gdb trace could be still useful. > > > > > Ok, will try to do it and post any interesting discoveries (I > can't find any core files, so I guess I'll have to try gdbing it) Core files should be in data directory somewhere. (If postmaster ulimit allows them.) > >>Anyway, I wanted to add hash functions (SHA-1 is already there, > >>so I'd like to add SHA-256 and SHA-512 at the very least, and > >>maybe also, for completeness, SHA-224 and SHA-384). > >> > > > >For SHA2 hashes it should be enough to compile pgcrypto > >against OpenSSL 0.9.8. Or upgrade to PostgreSQL 8.1, > >where they are included. > > > >Ofcourse, that is no fun. > > > > Hahahaha -- why do I keep being naive and making the same mistake > over and over!!! :-) > > As much as it is indeed no fun, it is also good to know (and I > didn't know that OpenSSL 0.9.8 had them either, so thanks for > the double pointer!) Hehe. If hacking on a project, it is usually good to check out the latest version. pgcrypto had a major update in 8.1. > >If you want to hack, you could try adding SHA224 to the SHA2 > >implementation in 8.1. > > > > Sounds like a plan :-) Cool! Look how SHA384 fits in and try to follow that. That way the code is in coherent style. -- marko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Stored procedure
On Thu, 2005-12-22 at 12:42 -0500, Jaime Casanova wrote:
> On 12/22/05, Ted Byers <[EMAIL PROTECTED]> wrote:
> >
> > INSERT INTO foo (auto,text)
> > VALUES(NULL,'text'); # generate ID by inserting NULL
>
> and this of course is bad... if a insert NULL i want the NULL to be inserted.
> SQL Standard way of doing things is "ommiting the auto incremental fld at all"
>
> INSERT INTO foo (text) VALUES ('text');
and then there is the god old DEFAULT value:
INSERT INTO foo (auto,text) VALUES(DEFAULT,'text');
gnari
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Inheritance Algebra
On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote: > On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: > > I would assume quite a few people would use table > > inheritance in a simple way were it available in a more > > convenient fashion: to transport fields, primary and foreign > > keys to child tables. > > I am not clear on why this sort of scenario benefits more from CREATE TABLE's > "INHERITS" clause than the "LIKE" clause Because the inherited fields are aggregated in the parent table. Imagine a database: create table narrative_base ( narrative text ); create table memo ( author text default CURRENT_USER ) inherits (narrative_base); create table ads ( fk_campaign integer references campaigns(pk) ) inherits (narrative_base); ... more child tables ... even more child tables Then we go on merrily inserting all sorts of stuff into the narrative_base child tables for two years. Now the boss asks me: "Has anyone ever written anything with 'PostgreSQL' in it in our company ?" So I go select tableoid, * from narrative_base where narrative ilike '%postgresql'; et voila. I don't have to remember all the tables potentially containing narrative and join them. Now, if this properly transporter primary and foreign keys to child tables I could add pk serial primary key to narrative_base and be done with primary keys for all children. Get the drift ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inheritance Algebra
On 12/23/05, Karsten Hilbert <[EMAIL PROTECTED]> wrote: > On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote: > > > On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: > > > > I would assume quite a few people would use table > > > inheritance in a simple way were it available in a more > > > convenient fashion: to transport fields, primary and foreign > > > keys to child tables. > > > > I am not clear on why this sort of scenario benefits more from CREATE > > TABLE's > > "INHERITS" clause than the "LIKE" clause > Because the inherited fields are aggregated in the parent > table. > > Imagine a database: > > create table narrative_base ( > narrative text > ); > > create table memo ( > author text default CURRENT_USER > ) inherits (narrative_base); > > create table ads ( > fk_campaign integer references campaigns(pk) > ) inherits (narrative_base); > > ... more child tables > > ... even more child tables We use something very similar to this to track user transactions (circulation of material, billings, etc.) in our (developing) ILS (Integrated Library System), OpenILS. But we take it even further with multiple levels of inheritance (simplified): CREATE TABLE payment ( pid serial, xact bigint, ptime timestamptz, pamount numeric(10,2) ); CREATE TABLE bnm_payment ( -- "brick-n-mortar" accepting_user int ) INHERITS (payment); CREATE TABLE bnm_desk_payment ( cash_drawer_id text ) INHERITS (bnm_payment); CREATE TABLE check_payment ( check_number text ) INHERITS (bnm_desk_payment); ... and so on ... > > Then we go on merrily inserting all sorts of stuff into the > narrative_base child tables for two years. > > Now the boss asks me: "Has anyone ever written anything with > 'PostgreSQL' in it in our company ?" > > So I go > > select tableoid, * from narrative_base where narrative ilike '%postgresql'; > > et voila. I don't have to remember all the tables > potentially containing narrative and join them. Precisely. We can report on daily payments at each of the "levels" all the way down to payment type, or just get a total for the cash drawers, or a grand total. Billing line items are structured similarly, so it's also very easy to grab a summary bill for a user and "explode" it for a detailed view using tableoid. > > Now, if this properly transporter primary and foreign keys > to child tables I could add > > pk serial primary key > > to narrative_base and be done with primary keys for all > children. > > Get the drift ? While I originally wanted this as well, by using a serial for the "pid" field in the root table you've essentially go that. While cross-table unique indexes aren't available now, I know that some smart people are thinking about them. Most of the time it comes up in relation to O*'s "global indexes" on partitioned tables, and in that sense is not of much use due to performance implications, but I think /our/ use makes a strong case for such a beast. That said, I believe I have a workaround that may suffice if you absolutely require constraint enforced globally unique PKEYs. This example uses the pid field from the root table (that is inherited everywhere) to track uniqueness. CREATE TABLE payment_entities ( id bigint primary key, toid oid -- tableoid ); CREATE FUNCTION global_unique_payment_entity RETURNS TRIGGER AS $$ BEGIN BEGIN insert into entities (id, toid) values (NEW.pid, TG_RELID); EXCEPTION WHEN UNIQUE_VIOLATION THEN RAISE EXCEPTION 'Ack! Key % already exists as a payment ID', NEW.pid; END; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER global_unique_entity_payment_trig BEFORE INSERT ON cash_payment FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity(); CREATE TRIGGER global_unique_entity_payment_trig BEFORE INSERT ON check_payment FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity(); CREATE TRIGGER global_unique_entity_payment_trig BEFORE INSERT ON credit_card_payment FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity(); etc... That doesn't cover UPDATEs of course, but that should be easy enough to do. It does, however, give you a simple "type" lookup table if you happen to have a pid in hand and want to know what it is. Thoughts? > > Karsten > -- > GPG key ID E4071346 @ wwwkeys.pgp.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Indices for select count(*)?
One way to conceptually tackle this count(*) issue would be to create a new index type for it. The index type would (logically) just need to implement insert and delete operations and keep a running count with a big lock around it. Users could then choose to trade off concurrent performance against the speed of count() by creating or dropping that index. Implementing that type of index might not even be that hard but convincing the planer and executor to use it without too many hardcoded cases seems more challenging. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query for a time interval
Michael Fuhr <[EMAIL PROTECTED]> writes: > Unless I'm missing something that wouldn't use an index either, > because the planner wouldn't know what value to compare start_date > against without hitting each row to find that row's time_to_live. > But something like this should be able to use an expression index > on (start_date + time_to_live): > WHERE start_date + time_to_live < now() Or perhaps simpler, get rid of time_to_live in favor of an end_date column, which could be indexed directly. 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
Re: [GENERAL] Indices for select count(*)?
Peter Eisentraut <[EMAIL PROTECTED]> writes: > One way to conceptually tackle this count(*) issue would be to create a new > index type for it. The index type would (logically) just need to implement > insert and delete operations and keep a running count with a big lock around > it. Users could then choose to trade off concurrent performance against the > speed of count() by creating or dropping that index. Implementing that type > of index might not even be that hard but convincing the planer and executor > to use it without too many hardcoded cases seems more challenging. It's not that easy --- in the MVCC world there simply isn't a unique count that is the right answer for every observer. But the idea of packaging a count(*) mechanism as an index type seems like it might be a good one. I don't think the planner objection need be taken too seriously: we already have a good big wart in there for recognizing MIN/MAX indexability, and this sort of transformation would fit pretty naturally with what's already done in planagg.c. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] HINT: Perhaps out of disk space?
I'm investigating a problem that happened last night and I would appreciate any recommendations. The logs indicate that the disks were full, but I truly doubt that since we only use about 14GB out of the available 65GB. I found entries like this in the logs: ERROR: could not write block 2354 of temporary file: No space left on device HINT: Perhaps out of disk space? ERROR: could not extend relation "parent_table": No space left on device HINT: Check free disk space. LOG: could not close temporary statistics file "/var/lib/postgres/data/global/pgstat.tmp.1464": No space left on device According to the logs, the problem went away after a reboot. I wonder if the kernel or the RAID device got confused and postgres was simply echoing what it was told. We run a couple hundred postgres servers and we have not seen this before (except when the disks truly were full). Everything is in the root filesystem, which has plenty of room. Filesystem 1K-blocks Used Available Use% Mounted on /dev/sda1 67756724 14344392 49970408 23% / tmpfs 1034768 0 1034768 0% /dev/shm PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12) Debian Sarge with Linux kernel 2.4.27-2-686-smp Dell PowerEdge 1800 Dell MegaRAID PERC 4/DC RAID Controller, 128MB cache w/BBU 2x SEAGATE Cheetah 10K.7 ST373207LC in RAID 1 (mirroring) Folks are a little jittery because our customers do very heavy business this month and we don't want frantic support calls when we should be drinking eggnog. -Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] HINT: Perhaps out of disk space?
Michael Adler <[EMAIL PROTECTED]> writes: > I'm investigating a problem that happened last night and I would > appreciate any recommendations. The logs indicate that the disks were > full, but I truly doubt that since we only use about 14GB out of the > available 65GB. > I found entries like this in the logs: > ERROR: could not write block 2354 of temporary file: No space left on device > HINT: Perhaps out of disk space? > > ERROR: could not extend relation "parent_table": No space left on device > HINT: Check free disk space. > > LOG: could not close temporary statistics file > "/var/lib/postgres/data/global/pgstat.tmp.1464": No space left on device > According to the logs, the problem went away after a reboot. I wonder > if the kernel or the RAID device got confused and postgres was simply > echoing what it was told. We run a couple hundred postgres servers and > we have not seen this before (except when the disks truly were full). I'm inclined to think that a query created a 50GB temporary file ... the postmaster cleans out temp files when restarted, so that would have destroyed the evidence. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Isolate Logs
Is it possible to isolate logging for each individual database? For example, lets say I have 3 databases: db1, db2, db3. And I want to log db activity (statements, login/logout, etc..) db1, db2, db3 like $PGDATA/db1, $PGDATA/db2, $PGDATA/db3, respectively. Currently everything is logged in $PGDATA/pg_log TIA ---(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: [GENERAL] Indices for select count(*)?
On Fri, Dec 23, 2005 at 11:04:50AM -0500, Tom Lane wrote: > It's not that easy --- in the MVCC world there simply isn't a unique > count that is the right answer for every observer. But the idea of > packaging a count(*) mechanism as an index type seems like it might be > a good one. I don't think the planner objection need be taken too > seriously: we already have a good big wart in there for recognizing > MIN/MAX indexability, and this sort of transformation would fit pretty > naturally with what's already done in planagg.c. AFAICS two big problems with using an index type: 1. The index isn't told when the tuple is deleted. 2. The server expects to be able to lookup an index. Other than that... -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpNuI5Y43VTf.pgp Description: PGP signature
Re: [GENERAL] Indices for select count(*)?
Martijn van Oosterhout writes: > AFAICS two big problems with using an index type: > 1. The index isn't told when the tuple is deleted. Hm, good point ... we could make it do so but for ordinary deletes it'd be a waste of cycles to open indexes at all. > 2. The server expects to be able to lookup an index. Only if there is a WHERE operator that matches the index's opclass. This hypothetical index type would probably have one dummy opclass containing no operators. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] HINT: Perhaps out of disk space?
On Fri, Dec 23, 2005 at 11:36:54AM -0500, Tom Lane wrote: > Michael Adler <[EMAIL PROTECTED]> writes: > > I'm investigating a problem that happened last night and I would > > appreciate any recommendations. The logs indicate that the disks were > > full, but I truly doubt that since we only use about 14GB out of the > > available 65GB. > > > I found entries like this in the logs: > > > ERROR: could not write block 2354 of temporary file: No space left on > > device > > HINT: Perhaps out of disk space? > > > > ERROR: could not extend relation "parent_table": No space left on device > > HINT: Check free disk space. > > > > LOG: could not close temporary statistics file > > "/var/lib/postgres/data/global/pgstat.tmp.1464": No space left on device > > > According to the logs, the problem went away after a reboot. I wonder > > if the kernel or the RAID device got confused and postgres was simply > > echoing what it was told. We run a couple hundred postgres servers and > > we have not seen this before (except when the disks truly were full). > > I'm inclined to think that a query created a 50GB temporary file ... > the postmaster cleans out temp files when restarted, so that would > have destroyed the evidence. I'm curious about what could have resulted in so much temporary storage for a database that fits entirely in 2.5GB space. I can imagine taking the largest table and joining it against itself many times without a WHERE clause. What else would use a lot of temp storage? How long would it take to clean out 50GB of temp files? It looks like the postmaster was able to start up instantly after the reboot (ready less than 1 second after "LOG: database system was shut down at...") I really appreciate any guidance you could offer. -Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] newbie : setting access for users in a web enviroment
Hi, I am new to postgres but coming from a MySQL enviroment. I am confused with the necessary steps to create users and restrict them to access/delete/insert/update data and create/delete/alter tables in a specific database. I've created a database test and a user testadm createdb test createuser -D -P testadm Enter password for new user: Enter it again: Shall the new user be allowed to create more new users? (y/n) n CREATE USER psql test \du List of users User name | User ID | Attributes | Groups +-++ testadm | 100 | | postgres | 1 | superuser, create database | GRANT CREATE,REFERENCES ON DATABASE test TO testadm; \z Access privileges for database "test" Schema | Name | Type | Access privileges +--+--+--- How can I specify that the user testadm can perform those actions to this database? Tks.
Re: [GENERAL] newbie : setting access for users in a web enviroment
"robert mena" <[EMAIL PROTECTED]> wrote > > How can I specify that the user testadm can perform those actions to this > database? Use GRANT command (the opposite is REVOKE). I suppose you are using 8.1: http://www.postgresql.org/docs/8.1/static/sql-grant.html Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] newbie : setting access for users in a web enviroment
Am Freitag, 23. Dezember 2005 22:06 schrieb robert mena: > GRANT CREATE,REFERENCES ON DATABASE test TO testadm; > > \z > Access privileges for database "test" > Schema | Name | Type | Access privileges > +--+--+--- > > How can I specify that the user testadm can perform those actions to this > database? For one thing, the command \z shows table privileges, so the empty table above is not surprising. pg_database would give you better information. Second, the privilege type REFERENCES does not exist for databases, only for tables, so the command you executed does not make sense. I suggest you peruse the GRANT manual page again. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Indices for select count(*)?
Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Fri, Dec 23, 2005 at 11:04:50AM -0500, Tom Lane wrote: > > It's not that easy --- in the MVCC world there simply isn't a unique > > count that is the right answer for every observer. But the idea of > > packaging a count(*) mechanism as an index type seems like it might be > > a good one. I don't think the planner objection need be taken too > > seriously: we already have a good big wart in there for recognizing > > MIN/MAX indexability, and this sort of transformation would fit pretty > > naturally with what's already done in planagg.c. > > AFAICS two big problems with using an index type: > > 1. The index isn't told when the tuple is deleted. > 2. The server expects to be able to lookup an index. > > Other than that... I think our TODO has a good summary of the issues: --- * Speed up COUNT(*) We could use a fixed row count and a +/- count to follow MVCC visibility rules, or a single cached value could be used and invalidated if anyone modifies the table. Another idea is to get a count directly from a unique index, but for this to be faster than a sequential scan it must avoid access to the heap to obtain tuple visibility information. * Add estimated_count(*) to return an estimate of COUNT(*) This would use the planner ANALYZE statistics to return an estimated count. * Allow data to be pulled directly from indexes Currently indexes do not have enough tuple visibility information to allow data to be pulled from the index without also accessing the heap. One way to allow this is to set a bit on index tuples to indicate if a tuple is currently visible to all transactions when the first valid heap lookup happens. This bit would have to be cleared when a heap tuple is expired. Another idea is to maintain a bitmap of heap pages where all rows are visible to all backends, and allow index lookups to reference that bitmap to avoid heap lookups, perhaps the same bitmap we might add someday to determine which heap pages need vacuuming. Frequently accessed bitmaps would have to be stored in shared memory. One 8k page of bitmaps could track 512MB of heap pages. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Indices for select count(*)?
Bruce Momjian writes: >> On Fri, Dec 23, 2005 at 11:04:50AM -0500, Tom Lane wrote: >>> It's not that easy --- in the MVCC world there simply isn't a unique >>> count that is the right answer for every observer. But the idea of >>> packaging a count(*) mechanism as an index type seems like it might be >>> a good one. > I think our TODO has a good summary of the issues: The point here was the idea that we might implement something like the delta-counts approach, but package it to look like a specialized index type --- as opposed to making the user create triggers and so on, which'd surely be a lot more error-prone to set up. Also, if it were an index type then it would be relatively straighforward to get the planner to recognize the availability of a substitute way of doing COUNT(*). We could do all this in other ways but it'd require more new infrastructure. The DELETE problem might kill the idea though. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Escaped backslash in SQL constant
Hello!
The database cluster is initialized to use UNICODE. The client encoding
is set to BIG5. The middleware escapes the backslash in the following
string before writing to TEXT/VARCHAR column in server:
a5 5c af e0
This is a string comprises Big5 characters each of 2 octets big. Note
that the second octets, 5c, of the first Big5 character clashes
backslash '\'. Thus, this string is escaped to
a5 5c 31 33 34 af e0
and is inserted to backend something like this:
INSERT INTO x VALUES ('y\134na');
Where octets 'y' and character "na" are unprintable characters here in
terms of ASCII.
Problem is that this string is stored exactly the same as the input: "a5
5c 31 33 34 af e0" instead of "a5 5c af e0". The SELECT outputs the
escaped string (7 octets) instead of the unescaped string (4 octets),
too.
However, the server manages the following string differently:
INSERT INTO x VALUES ('A\134B');
and
SELECT * FROM x;
outputs
A\B
Its size stored in the column is 3 octets. This second case is exactly
what I need. I guess strings like "C:\dir" is properly processed too
though I did not test it.
Why server treats the first string in this undesired way?
Regards,
CN
--
http://www.fastmail.fm - Email service worth paying for. Try it for free
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Escaped backslash in SQL constant
"CN" <[EMAIL PROTECTED]> writes: > The database cluster is initialized to use UNICODE. The client encoding > is set to BIG5. The middleware escapes the backslash in the following > string before writing to TEXT/VARCHAR column in server: > a5 5c af e0 Seems to me that you need to fix your broken middleware --- it has no business doing that. > This is a string comprises Big5 characters each of 2 octets big. Note > that the second octets, 5c, of the first Big5 character clashes > backslash '\'. Thus, this string is escaped to > a5 5c 31 33 34 af e0 Now you have a 2-octet Big5 character, followed by 3 1-octet ASCII digits, followed by a 2-octet Big5 character. All the subsequent processing is doing what it should with this, AFAICS. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Escaped backslash in SQL constant
Many thanks for the lightening fast answer! > > The database cluster is initialized to use UNICODE. The client encoding > > is set to BIG5. The middleware escapes the backslash in the following > > string before writing to TEXT/VARCHAR column in server: > > > a5 5c af e0 > > Seems to me that you need to fix your broken middleware --- it has no > business doing that. Are you suggesting that the middleware should not escape backslashes in the first place? The doc in http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS reads: "Any other character following a backslash is taken literally. Thus, to include a backslash in a string constant, write two backslashes." My understanding on this statement is that escaping \ to \\ blindly for any string and any encoding hurts nothing. Also, I thought that escaping "\" to "\134" for TEXT column should be always equivalent to escaping it to "\\", at least with this current (8.1) version. > > This is a string comprises Big5 characters each of 2 octets big. Note > > that the second octets, 5c, of the first Big5 character clashes > > backslash '\'. Thus, this string is escaped to > > > a5 5c 31 33 34 af e0 > > Now you have a 2-octet Big5 character, followed by 3 1-octet ASCII > digits, followed by a 2-octet Big5 character. All the subsequent > processing is doing what it should with this, AFAICS. What I still don't realize is that as mentioned in my first posting, "A\134B" gets stored in TEXT with value "A\B" but the Big5 string escaped in the same manner yeids different result - the latter string is stored in escaped form and becomes longer than intended. I thought two 2-octet Big5 characters would be stored in backend. Regards, CN -- http://www.fastmail.fm - IMAP accessible web-mail ---(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: [GENERAL] Escaped backslash in SQL constant
"CN" <[EMAIL PROTECTED]> writes: >> Seems to me that you need to fix your broken middleware --- it has no >> business doing that. > Are you suggesting that the middleware should not escape backslashes in > the first place? No, I'm suggesting that it shouldn't be let loose on Big5 data when it evidently hasn't a clue about that encoding. The byte in question *is not* a backslash, it's not even an independent character; and so changing it on the assumption that it is logically a backslash simply breaks the data. Your quickest route to a solution may be to avoid Big5 in favor of an encoding that is ASCII-safe, such as UTF8. You can feed that through code that only understands ASCII with much less risk than an encoding where second and later bytes might look like ASCII. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Escaped backslash in SQL constant
> No, I'm suggesting that it shouldn't be let loose on Big5 data when it
> evidently hasn't a clue about that encoding. The byte in question
> *is not* a backslash, it's not even an independent character; and so
> changing it on the assumption that it is logically a backslash simply
> breaks the data.
Would you please enlighten me the behavior of the backend - why
SET CLIENT_ENCODING TO Big5;
INSERT INTO y VALUES ('A\134B');
stores
A\B
while
INSERT INTO y VALUES ('y\134na');
--"y\" and "na" are two Big5 characters.
stores
y\134na
instead of
y\na
> Your quickest route to a solution may be to avoid Big5 in favor of
> an encoding that is ASCII-safe, such as UTF8. You can feed that through
> code that only understands ASCII with much less risk than an encoding
> where second and later bytes might look like ASCII.
Are you suggesting me to implement the middleware that will translate
Big5 input to UTF8 and then escape the latter before sending it to
PostgreSQL?
SET CLIENT_ENCODING TO UTF8;
[BIG5 string from user] --> [middleware] --> [UTF8] --> [escaped UTF8]
--> PostgreSQL (initdb with -E UNICODE)
Best regards,
CN
--
http://www.fastmail.fm - Does exactly what it says on the tin
---(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: [GENERAL] Escaped backslash in SQL constant
"CN" <[EMAIL PROTECTED]> writes:
> INSERT INTO y VALUES ('y\134na');
> --"y\" and "na" are two Big5 characters.
I'm not sure how to explain it any more clearly: the backslash in this
example is not a backslash. It's a byte within a multibyte character,
which *entirely coincidentally* happens to have the same numeric value
as an ASCII backslash. But it isn't a backslash. And it won't be
processed as a backslash by any Big5-aware code.
Code that does not understand about multibyte characters is simply
unsafe to apply to data that is in Big5. You need to fix that
middleware to understand Big5 encoding; or if that seems impractical,
switch to using another encoding for the data the middleware sees.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Escaped backslash in SQL constant
Thank you again for the clarification! > I'm not sure how to explain it any more clearly: the backslash in this > example is not a backslash. It's a byte within a multibyte character, > which *entirely coincidentally* happens to have the same numeric value > as an ASCII backslash. But it isn't a backslash. And it won't be > processed as a backslash by any Big5-aware code. If I understand this explanation correctly, then you have given me the answer I needed! Please correct me if my understanding that follows is wrong again: PostgreSQL is Big5-aware code. It does not blindly unescape every backlash it encounters in SQL literals. Instead, it sees backslash as part of some Big5 characters as they are supposed to be when client encoding is set to Big5 (SET CLIENT_ENCODING TO BIG5). In other words, PostgreSQL sees the backslash in "C:\134" differently from that being part of multi-byte characters depending on which client encoding is used. Regards, CN -- http://www.fastmail.fm - And now for something completely different� ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] query for a time interval
On Wed, Dec 21, 2005 at 11:52:56 -0800,
Mark <[EMAIL PROTECTED]> wrote:
> Hello everybody,
>
> I'm looking for an elegant SQL statement that will work in
> Postgresql, MySQL and ORACLE.
> The query will be executed by Java client.
>
> To have this query for Postgresql is priority number one.
>
>
> In this query I try to get a list of message Ids that expired.
>
> time_to_live is in seconds.
>
> SELECT id
> FROM mq
> WHERE now - start_date > time_to_live;
An interval is not going to be comparable to an integer without casting.
If you use explicit casting the query probably won't be portable. If there
is some implicit cast that makes the above SQL valid, it is probably a cast
to text which won't do what you want.
>
> I have a following table:
>
> CREATE TABLE mq
> {
> msg_id INTEGER,
> retry_date TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
> start_date TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
> time_to_live INTEGER
> }
Can you make time_to_live an interval?
>
> Thanks!
> Mark.
>
> __
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---(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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
