[SQL] primary keys as TEXT
Hi. There can be performancs problems in having primary keys of type TEXT? What about having a primary key of 3 columns (all of type TEXT)? Regards Manlio Perillo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] primary keys as TEXT
On Jul 28, 2006, at 17:37 , Manlio Perillo wrote: There can be performancs problems in having primary keys of type TEXT? What about having a primary key of 3 columns (all of type TEXT)? What defines a problem in terms of performance is heavily dependent on your particular needs and requirements. What are your requirements? What profiling have you done to see where your performance bottlenecks may be? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 19:26 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I run the command responsible for creating the index and I entered > "continue" in gdb for executing the command. After a while, the server > crashes: > > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) > > Continuing. > > > Program terminated with signal SIGSEGV, Segmentation fault. > > The program no longer exists. > > > I can't do "bt" since the program no longer exists. > > I think you typed one carriage return too many and the thing re-executed > the last command, ie, the continue. Try it again. > You were right. Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) bt #0 0x08079e2a in slot_attisnull () #1 0x0807a1d0 in slot_getattr () #2 0x080c6c73 in FormIndexDatum () #3 0x080c6ef1 in IndexBuildHeapScan () #4 0x0809b44d in btbuild () #5 0x0825dfdd in OidFunctionCall3 () #6 0x080c4f95 in index_build () #7 0x080c68eb in index_create () #8 0x08117e36 in DefineIndex () #9 0x081db4ee in ProcessUtility () #10 0x081d8449 in PostgresMain () #11 0x081d99d5 in PortalRun () #12 0x081d509e in pg_parse_query () #13 0x081d6c33 in PostgresMain () #14 0x081aae91 in ClosePostmasterPorts () #15 0x081ac14c in PostmasterMain () #16 0x08168f22 in main () -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL server terminated by signal 11
"Daniel Caune" <[EMAIL PROTECTED]> writes: > Program received signal SIGSEGV, Segmentation fault. > 0x08079e2a in slot_attisnull () > (gdb) bt > #0 0x08079e2a in slot_attisnull () > #1 0x0807a1d0 in slot_getattr () > #2 0x080c6c73 in FormIndexDatum () > #3 0x080c6ef1 in IndexBuildHeapScan () > #4 0x0809b44d in btbuild () > #5 0x0825dfdd in OidFunctionCall3 () > #6 0x080c4f95 in index_build () > #7 0x080c68eb in index_create () > #8 0x08117e36 in DefineIndex () Hmph. gdb is lying to you, because slot_getattr doesn't call slot_attisnull. This isn't too unusual in a non-debug build, because the symbol table is incomplete (no mention of non-global functions). Given that this doesn't happen right away, but only after it's been processing for awhile, we can assume that FormIndexDatum has been successfully iterated many times already, which seems to eliminate theories like the slot or the keycol value being bogus. I'm pretty well convinced now that we're looking at a problem with corrupted data. Can you do a SELECT * FROM (or COPY FROM) the table without error? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL server terminated by signal 11
> De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : vendredi, juillet 28, 2006 09:38 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) bt > > #0 0x08079e2a in slot_attisnull () > > #1 0x0807a1d0 in slot_getattr () > > #2 0x080c6c73 in FormIndexDatum () > > #3 0x080c6ef1 in IndexBuildHeapScan () > > #4 0x0809b44d in btbuild () > > #5 0x0825dfdd in OidFunctionCall3 () > > #6 0x080c4f95 in index_build () > > #7 0x080c68eb in index_create () > > #8 0x08117e36 in DefineIndex () > > Hmph. gdb is lying to you, because slot_getattr doesn't call > slot_attisnull. > This isn't too unusual in a non-debug build, because the symbol table is > incomplete (no mention of non-global functions). > > Given that this doesn't happen right away, but only after it's been > processing for awhile, we can assume that FormIndexDatum has been > successfully iterated many times already, which seems to eliminate > theories like the slot or the keycol value being bogus. I'm pretty well > convinced now that we're looking at a problem with corrupted data. Can > you do a SELECT * FROM (or COPY FROM) the table without error? > > regards, tom lane The statement "copy gslog_event to stdout;" leads to "ERROR: invalid memory alloc request size 4294967293" after awhile. (...) 354964834 2006-07-19 10:53:42.813+00 (...) 354964835 2006-07-19 10:53:44.003+00 (...) ERROR: invalid memory alloc request size 4294967293 I tried then "select * from gslog_event where gslog_event_id >= 354964834 and gslog_event_id <= 354964900;": 354964834 | 2006-07-19 10:53:42.813+00 | (...) 354964835 | 2006-07-19 10:53:44.003+00 | (...) 354964837 | 2006-07-19 10:53:44.113+00 | (...) 354964838 | 2006-07-19 10:53:44.223+00 | (...) (...) (66 rows) The statement "select * from gslog_event;" leads to "Killed"... Ouch! The psql client just exits (the postgres server crashes too)! The statement "select * from gslog_event where gslog_event_id <= 354964834;" passed. I did other tests on some other tables that contain less data but that seem also corrupted: copy player to stdout ERROR: invalid memory alloc request size 1918988375 select * from player where id >=771042 and id<=771043; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player where id <= 771042; max - 15 select max(length(username)) from player where id >= 771050; max - 15 select max(length(username)) from player where id >= 771044 and id <= 771050; max - 13 Finally: select * from player where id=771043; ERROR: invalid memory alloc request size 1918988375 select id from player where id=771043; id 771043 (1 row) agora=> select username from player where id=771043; ERROR: invalid memory alloc request size 1918988375 I'm also pretty much convinced that there are some corrupted data, especially varchar row. Before dropping corrupted rows, is there a way to read part of corrupted data? Thanks Tom for your great support. I'm just afraid that I wasted your time... Anyway I'll write a FAQ that provides some information about this kind of problem we have faced. Regards, -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] PostgreSQL server terminated by signal 11
"Daniel Caune" <[EMAIL PROTECTED]> writes: > The statement "copy gslog_event to stdout;" leads to "ERROR: invalid memory > alloc request size 4294967293" after awhile. > ... > I did other tests on some other tables that contain less data but that seem > also corrupted: This is a bit scary as it suggests a systemic problem. You should definitely try to find out exactly what the corruption looks like. It's usually not hard to home in on where the first corrupted row is --- you do SELECT ctid, * FROM tab LIMIT n; and determine the largest value of n that won't trigger a failure. The corrupted region is then just after the last ctid you see. You can look at those blocks with "pg_filedump -i -f" and see if anything pops out. Check the PG archives for previous discussions of dealing with corrupted data. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Disk is full, what's cool to get rid of?
Tom Lane wrote: Andrew Sullivan <[EMAIL PROTECTED]> writes: If the data isn't critical, you maybe could truncate a table to clear enough space. Deleting anything under pg_xlog is more or less guaranteed to mean your database is garbage. If you're desperate you could shut down the postmaster, run pg_resetxlog, restart the postmaster. This would cut xlog contents to the minimum --- however, they'd probably soon grow back to whatever they are now, so it's not much of a long-term solution. It might give you some breathing room to look for other places to trim though. If the database hasn't been maintained well then you likely are suffering from table bloat and/or index bloat. A desperation measure for fixing that is drop all indexes, vacuum full, recreate all indexes. (There are other procedures you may find recommended, such as CLUSTER, but they have transient disk-space requirements that you're not gonna be able to afford when your back is to the wall.) Thanks for your suggestions. Doing the tune2fs trick worked enough for me to get a vacuum full to run -- which then pointed out the fact that max_fsm_pages was still running off of the default 1000 (doing a SELECT COUNT(*) FROM pg_class; showed over 100k relations) so, I made some much needed tweaks to postresql.conf, restarted the db and then restarted the vacuum full (it's still running now). As far as the rest of the suggestion (CLUSTER, index drop/add, etc...), there really isn't much of a point as this database is for development only and is only going to be around for about another month when we build a whole new pristine development db and environment from the ground up (I can't wait!), but these are all good things to know. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] return setof records
On Thu, Jul 27, 2006 at 03:41:31PM -0500, Chris Lukenbill wrote: > Everywhere I've looked the agreement was that making a call to the > function had to be done as follows: > > SELECT * FROM sp_frontalerts_summary(1,'2006-07-27 18:08:09','2006-07-27 > 19:58:15' ) as (numitems int, region int); That's one way. Another way would be for the function to use OUT parameters (if you're running 8.1) or to return a custom type so you could omit the column definition list. Then the query would be: SELECT * FROM sp_frontalerts_summary(1, '2006-07-27 18:08:09', '2006-07-27 19:58:15'); > So there isn't a question on that. My question is, how do I return a > "setof record" back to the application. The function and how you're calling it look correct except that count() returns bigint, not int. You didn't mention what error you're getting but I'd guess it's "wrong record type supplied in RETURN NEXT". Try using "numitems bigint" instead of "numitems int". -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] return setof records
I think that actually solved the problem. The fact that I was sending back a bigint. One of those things that's hard to spot when I don't know if I was on the right track to begin with. Thank you everyone for your help. ChrisOn 7/28/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Thu, Jul 27, 2006 at 03:41:31PM -0500, Chris Lukenbill wrote:> Everywhere I've looked the agreement was that making a call to the> function had to be done as follows:>> SELECT * FROM sp_frontalerts_summary(1,'2006-07-27 18:08:09','2006-07-27 > 19:58:15' ) as (numitems int, region int);That's one way. Another way would be for the function to use OUTparameters (if you're running 8.1) or to return a custom type soyou could omit the column definition list. Then the query would be: SELECT *FROM sp_frontalerts_summary(1, '2006-07-27 18:08:09', '2006-07-27 19:58:15');> So there isn't a question on that. My question is, how do I return a> "setof record" back to the application. The function and how you're calling it look correct except thatcount() returns bigint, not int. You didn't mention what erroryou're getting but I'd guess it's "wrong record type supplied inRETURN NEXT". Try using "numitems bigint" instead of "numitems int". --Michael Fuhr
Re: [SQL] Storing an ordered list
On 7/26/06, Michael Artz <[EMAIL PROTECTED]> wrote: On 7/26/06, Bruno Wolff III <[EMAIL PROTECTED]> wrote:> If you use numeric instead of int, then it is easy to insert new values.Hmm, hadn't thought about that. How would you normally implement it? I'm thinking that, if I wanted to insert between A and B, I could take(A.order + B.order)/2, which would be pretty simple. Is there abetter way? This is a good idea. Then you can add a scheduled process to read through these values and turn them back to integer values on a regular basis (sort of a reindexing) to keep your numbers from becoming small enough that you start experiencing round off problems. Perhaps you could add a trigger that says if the value entered into the order field is going out to too many decimal places, it renumbers everything. to keep the values clean. Or better yet, add a stored procedure you call to reorder the elements that decides how to do it for you so you can easily rewrite the implementation without having to change the application. Just some ideas...== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ==
Re: [SQL] primary keys as TEXT
On 7/28/06, Manlio Perillo <[EMAIL PROTECTED]> wrote: Hi.There can be performancs problems in having primary keys of type TEXT?What about having a primary key of 3 columns (all of type TEXT)? If you are really worried about it, why not just use surrogate keys? They are very easy to use. Then your problem is solved. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] primary keys as TEXT
On Fri, 2006-07-28 at 03:37, Manlio Perillo wrote: > Hi. > > There can be performancs problems in having primary keys of type TEXT? > What about having a primary key of 3 columns (all of type TEXT)? The biggest problem with using text as a primary key or foreign key is that text types are locale dependent, so that you might get one behaviour on one server and another behaviour on another, depending on configuration of the locale and the locale support on that machine. For instance, if you have a locale that says that E and e are equivalent, and another locale that says they aren't... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] primary keys as TEXT
Michael Glaesemann ha scritto: > > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote: > >> There can be performancs problems in having primary keys of type TEXT? >> What about having a primary key of 3 columns (all of type TEXT)? > > What defines a problem in terms of performance is heavily dependent on > your particular needs and requirements. What are your requirements? What > profiling have you done to see where your performance bottlenecks may be? > I still don't have done profiling. Simply in the first version of my schema I used serial keys but the result is ugly and it force me to do a lot of joins. Thanks and regards Manlio Perillo ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] UPDATE with WHERE clause on joined table
Hi, I'm only very rarely using SQL, so please forgive me if I show any obvious signs of ignorance... I've got three tables "customer", "address" and "country". I want to set the "language" attribute on "customer" on rows returned by a SELECT such as this: SELECT title, first_names, last_name, email, language, country.country_name FROM ((customer JOIN address ON customer.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE email LIKE '%.es' AND country.country_name = 'SPAIN'; That is, I want to set the "language" to 'Spanish' where the "customer.email" is like '%.es' and where "country.country_name" is 'SPAIN'. I've tried all sorts of places to put the JOIN and the WHERE clauses within the UPDATE statement, but I just don't get it. I'd be most grateful for any help... TIA Fabian P.S.: One of my sorry attempts looked like this - which updates all rows in "customer" so I figure the WHERE clause is not where it should be: UPDATE customer SET language = 'Spanish' FROM ((customer AS customer_address JOIN address ON customer_address.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE customer.email LIKE '%.es' AND country.country_name = 'SPAIN'); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] UPDATE with WHERE clause on joined table
Fabian Peters wrote: Hi, I'm only very rarely using SQL, so please forgive me if I show any obvious signs of ignorance... I've got three tables "customer", "address" and "country". I want to set the "language" attribute on "customer" on rows returned by a SELECT such as this: SELECT title, first_names, last_name, email, language, country.country_name FROM ((customer JOIN address ON customer.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE email LIKE '%.es' AND country.country_name = 'SPAIN'; That is, I want to set the "language" to 'Spanish' where the "customer.email" is like '%.es' and where "country.country_name" is 'SPAIN'. I've tried all sorts of places to put the JOIN and the WHERE clauses within the UPDATE statement, but I just don't get it. I'd be most grateful for any help... TIA Fabian P.S.: One of my sorry attempts looked like this - which updates all rows in "customer" so I figure the WHERE clause is not where it should be: UPDATE customer SET language = 'Spanish' FROM ((customer AS customer_address JOIN address ON customer_address.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE customer.email LIKE '%.es' AND country.country_name = 'SPAIN'); The FROM clause is where you put relations other than the one you are updating. Try this: UPDATE customer SET language='Spanish' FROM address ad, country co WHERE customer.eoid=ad.eoid_customer AND ad.eoid_country=co.eoid AND co.country_name='SPAIN' AND customer.email LIKE '%.es'; Note that for demonstration purposes I've aliased the join tables and that (unfortunately) you can't alias the update table. erik jones <[EMAIL PROTECTED]> software development emma(r) ---(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
