Re: [GENERAL] bytea characteristics
jws wrote: > Do the images take up a certain percentage more space due to the on- > disk format when stored this way? Bytes are pretty much stored just as bytes, with four bytes of overhead for the length field. Larger values (> 2kB) are stored out of line, so there really shouldn't be much concern about storing the image data in the database. It's probably more of a question what makes your processing easier. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
[GENERAL] Trouble w/plperl sproc on red hat 9
Not sure if this one is fixable, but a user of my GPL'd package was unable to run our install. Eventually we pinned it down to a failed load of a sproc written in plperl. He says he's running a Red Hat 9 system with Postgres 8.1 and perl is 5.8.5. When he takes the plperl sproc and attempts to load it through pgsql he gets: SQL ERROR : server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Does this ring any bells w/regard to possible version mismatches or known issues? begin:vcard fn:Kenneth Downs n:Downs;Kenneth adr;dom:;;347 Main Street;East Setauket;NY;11733 email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE url:http://www.secdat.com version:2.1 end:vcard ---(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] Adding TEXT columns tanks performance?
On Feb 9, 2007, at 11:43 AM, Merlin Moncure wrote:
On 2/8/07, Arturo Perez <[EMAIL PROTECTED]> wrote:
Hi all,
Saturday I changed a table to add a varchar(24) and a TEXT column.
It's used for some reporting purposes (small potatoe stuff really)
and the TEXT column remains mostly empty. However, this week
performance has gotten terrible. Queries joining against the
aforementioned table have gone from 40s to 1500s. The schema change
is the only explanation I have for the 30x slower queries. The
queries have definitely gotten disk-bound (I can see the connection
process sitting in the D state for several minutes).
This is pg8.1.4 on Linux RedHat. Would adding a TEXT column impact
things that much? The change to the schema was
alter table foo add column title text;
explain analyze please.
no reason for adding text column to do that. especially since you
didn't default the column which would effectively update the entire
table.
merlin
Here's the explain analyze. Note that this query was never very fast
but's it has literally gotten two orders of magnitude slower. This is
with 8.1.4 on linux with nothing special in terms of disks (I think
it's a mirrored system drive). It's a hosted environment kind of thing.
Shared buffers is 160MB (2) and effective cache is 1GB. The
user_tracking
table has about 2M rows and the extended_user table has about 6K.
I'm not very
good at reading these but it looks like sort memory might be too
low? work_mem is 1024,
the default. I did this "set session work_mem to 10;" in psql
but it still takes
quite a while.
iht=> explain analyze SELECT session_id, action, count(ACTION) as hits
iht-> FROM extended_user LEFT JOIN
user_tracking USING (user_id)
iht-> WHERE subscription_id = 1147
iht-> GROUP BY session_id, action
iht-> HAVING count(ACTION) > 0;
QUERY PLAN
--
GroupAggregate (cost=172717.49..173695.46 rows=32599 width=60)
(actual time=411713.041..411761.857 rows=7309 loops=1)
Filter: (count("action") > 0)
-> Sort (cost=172717.49..172798.99 rows=32599 width=60) (actual
time=411712.907..411732.032 rows=16012 loops=1)
Sort Key: user_tracking.session_id, user_tracking."action"
-> Merge Left Join (cost=0.00..169571.78 rows=32599
width=60) (actual time=147593.828..411070.706 rows=16012 loops=1)
Merge Cond: ("outer".user_id = "inner".user_id)
-> Index Scan using extended_user_pkey on
extended_user (cost=0.00..236.92 rows=117 width=4) (actual
time=1.627..154.499 rows=119 loops=1)
Filter: (subscription_id = 1147)
-> Index Scan using user_tracking_user_id_idx on
user_tracking (cost=0.00..164008.04 rows=2000218 width=64) (actual
time=0.010..408731.064 rows=2000620 loops=1)
Total runtime: 411781.174 ms
(10 rows)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] drop table if exists mytable;
Hi, I need to do a "drop table if exists" type thing. I realise I can easily look in pg_tables, but for testing (if), don't I need to use a procedural language? In which case, I will need to install it if it doesn't exist - but I don't know how to test to see whether a language exists without using a language! Am I missing something simple here? Is there no way to do this outside of a function? Cheers Antoine ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] drop table if exists mytable;
I am not sure if I understood the problem correctly!!! Can you not use the standard command "DROP TABLE IF EXISTS table1" that PG provides? http://www.postgresql.org/docs/8.2/interactive/sql-droptable.html Or is it that you are on a version of PG where "IF EXISTS" syntax is not available? Regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com On 2/10/07, Anton Melser <[EMAIL PROTECTED]> wrote: Hi, I need to do a "drop table if exists" type thing. I realise I can easily look in pg_tables, but for testing (if), don't I need to use a procedural language? In which case, I will need to install it if it doesn't exist - but I don't know how to test to see whether a language exists without using a language! Am I missing something simple here? Is there no way to do this outside of a function? Cheers Antoine
Re: [GENERAL] drop table if exists mytable;
Anton Melser <[EMAIL PROTECTED]> schrieb: > Hi, > I need to do a "drop table if exists" type thing. I realise I can Install 8.2 or use this function, posted by David Fetter: -- -- posted by David Fetter -- CREATE OR REPLACE FUNCTION drop_table(TEXT) RETURNS VOID STRICT LANGUAGE plpgsql AS $$ BEGIN BEGIN EXECUTE 'DROP TABLE ' || $1; EXCEPTION WHEN UNDEFINED_TABLE THEN /* do nothing */ RETURN; END; RETURN; END; $$; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trouble w/plperl sproc on red hat 9
Kenneth Downs <[EMAIL PROTECTED]> writes: > Not sure if this one is fixable, but a user of my GPL'd package was > unable to run our install. > > Eventually we pinned it down to a failed load of a sproc written in > plperl. He says he's running a Red Hat 9 system with Postgres 8.1 and > perl is 5.8.5. When he takes the plperl sproc and attempts to load it > through pgsql he gets: > > > SQL ERROR : server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > > Does this ring any bells w/regard to possible version mismatches or > known issues? It would probably be helpful to get a backtrace of the core dump generated by the crashing backend. -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trouble w/plperl sproc on red hat 9
Kenneth Downs <[EMAIL PROTECTED]> writes: > Eventually we pinned it down to a failed load of a sproc written in > plperl. He says he's running a Red Hat 9 system with Postgres 8.1 and > perl is 5.8.5. When he takes the plperl sproc and attempts to load it > through pgsql he gets: > SQL ERROR : server closed the connection unexpectedly It should work if plperl was compiled with that specific Perl version. If he's attempting to use an RPM built against a different Perl version, maybe not so much --- I'm not sure how ABI-compatible Perl is across versions. regards, tom lane ---(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] Adding TEXT columns tanks performance?
Arturo Perez <[EMAIL PROTECTED]> writes: > Saturday I changed a table to add a varchar(24) and a TEXT column. You didn't actually say which of these tables you changed? > I'm not very good at reading these but it looks like sort memory might > be too low? The runtime seems to be entirely in the index scan on user_tracking. I'm surprised it doesn't do something to avoid a full-table indexscan --- in this case, hashing with extended_user as the inner relation would seem like the obvious thing. Is user_id a hashable datatype? It's possible that adding the columns would have affected the plan by making it look like a sort or hash would take too much memory, but if that were it then your hand increase in work_mem should have fixed it. Tis odd. I don't suppose you know what plan was used before? regards, tom lane ---(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
[GENERAL] MOVE cursor in plpgsql?
Hi all, Is there a way to move a cursor in plpgsql in the same way as in regular sql? The function below would like to move the cursor back to the start each time the cursor runs out of rows, creating pairs of integers that are randomly put together. The "motivation" for this is to randomly assign parts of a shakespearian play (N=25) to not enough actors (N=6), giving each actor several parts. (To be truly fair, I would have to weight by number of lines, but that is for version 2... ) If there is a more graceful way to solve the problem, I am interested, but I would like to know about the MOVE issue in any case. CREATE TYPE int_pair as (i int, j int); CREATE OR REPLACE FUNCTION ASSIGN_RAND (tab1_nm text, tab2_nm text) RETURNS SETOF int_pair AS $PROC$ DECLARE curs1 REFCURSOR; tab1_id int; tab2_id int; id_pair int_pair; BEGIN OPEN curs1 FOR EXECUTE 'select id from (select id, random() as r from ' || quote_ident($2) || ' order by r) x'; FOR tab1_id IN EXECUTE 'select id from (select id, random() as r from ' || quote_ident($1) || ' order by r) x' LOOP id_pair.i := tab1_id; fetch curs1 into id_pair.j; IF NOT FOUND THEN MOVE FIRST IN curs1; -- XXX gives error! fetch curs1 into id_pair.j; END IF; RETURN NEXT id_pair; END LOOP; END; $PROC$ language plpgsql; ---(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] drop table if exists mytable;
> I need to do a "drop table if exists" type thing. I realise I can Install 8.2 or use this function, posted by David Fetter: Thanks for your answers... so this really was something that was missing (I think it a little rich to come out with a "are you using a version without this" when it has just come out!) ? I am trying to add postgres support for alchemi (.net grid infrastructure), and I wanted to change as little as possible. Seeing as it didn't support postgres before, I suppose having 8.2 as a prereq ain't to bad. Cheers Antoine ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Password Policy
Hi list (my first post), Is there any password polity that postgresql implement ? It is possible to put a set all no administrators passwords to = '123456' from times and times ? Has anyone implement a dinamic password autentication (the password changes according the date/month etc of a day ) ? Is there any function to encript, decript plain text in PostgreSQL and if it is aplicable ? Regards ... Ezequias
[GENERAL] Priorities for users or queries?
Hi, Is there a way to give priorities to queries or users? Something similar to NICE in Linux. My goal is to give the updating (backend) application a very low priority and give the web application a high priority to avoid disturbing the user experience. Thanks in advance! Benjamin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Problem indexing large database with large number of inserts (partial index building?)
Hi, I have a database (200GB+), I need to upload about 10GB of data each week. There are no deletions. My problem is that inserting takes a very long time due to the indexes. I can speedup inserting the data insertion if I drop the indexes but then I am left with the problem of rebuilding all of the indexes after the insertions have completed. Currently, I am doing some optimizations such as batching large number of inserts into a single commit and I have shut fsync off (fsync=off is ok for my project). At this point I am looking for some way to efficiently insert the data without having to rebuild the indexes every time. Is there some way to insert data but defer the INDEX building of new records until the end of a commit or something similar? Any other suggestions would be greatly appreciated. Benjamin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] win32 install fail
oblfolks I can't install postgresql on winxp profesional Installer fail ( creating cluster) Installer with out cluster . ok creating cluster on hand .fail initdb The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale Spanish_Argentina.28605. fixing permissions on existing directory C:/Archivos de programa/PostgreSQL/8.2/data ... ok creating subdirectories ... initdb: could not create directory "C:/Archivos de programa": File exists initdb: removing contents of data directory "C:/Archivos de programa/PostgreSQL/8.2/data" ever error is File exists Any clue? best regards MDC __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(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] [ADMIN] Priorities for users or queries?
Benjamin Arai wrote: > Hi, > > Is there a way to give priorities to queries or users? Something > similar to NICE in Linux. My goal is to give the updating (backend) > application a very low priority and give the web application a high > priority to avoid disturbing the user experience.\ Nope :) Joshua D. Drake > > Thanks in advance! > > Benjamin > > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > >http://www.postgresql.org/about/donate > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] does anyone have a tool to convert SP'sT-SQL to Postgres
Oracle provides a free tool for converting TSQL into PL/SQL. You can then use the PL/SQL on an EnterpriseDB database, if this doesn't work for ya... PL/SQL is quite a bit closer to PLpgSQL than TSQL is so you'll be well on your way. --Luss http://www.enterprisedb.com On 2/9/07, johnf <[EMAIL PROTECTED]> wrote: On Friday 09 February 2007 04:43, A. Kretschmer wrote: > am Thu, dem 08.02.2007, um 21:28:08 -0800 mailte johnf folgendes: > > Hi, > > I'm hoping someone has already taken the time to write a routine (in some > > language - python,perl, etc..) to convert MS SQL T-SQL stored procedures > > into Postgres PL/pgSQL. And of course they are willing to share. > > See there: http://www.postgresql.org/docs/techdocs.3 > > I hope, you can find what you need. > > > Andreas Thanks the link is helpful. It's just I was hoping to find a script that could care of most of the convert and just needed a little hand coding. -- John Fabiani ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] does anyone have a tool to convert SP'sT-SQL to Postgres
Denis Lussier wrote: > Oracle provides a free tool for converting TSQL into PL/SQL. > > You can then use the PL/SQL on an EnterpriseDB database, if this doesn't > work for ya... PL/SQL is quite a bit closer to PLpgSQL than TSQL is so > you'll be well on your way. There is also the open source Orafce project that can help you with a lot of the oracle specific functions. You can find it on pgfoundry -- http://www.pgfoundry.org/ Joshua D. Drake > > --Luss > http://www.enterprisedb.com > > On 2/9/07, johnf <[EMAIL PROTECTED]> wrote: >> >> On Friday 09 February 2007 04:43, A. Kretschmer wrote: >> > am Thu, dem 08.02.2007, um 21:28:08 -0800 mailte johnf folgendes: >> > > Hi, >> > > I'm hoping someone has already taken the time to write a routine (in >> some >> > > language - python,perl, etc..) to convert MS SQL T-SQL stored >> procedures >> > > into Postgres PL/pgSQL. And of course they are willing to share. >> > >> > See there: http://www.postgresql.org/docs/techdocs.3 >> > >> > I hope, you can find what you need. >> > >> > >> > Andreas >> >> Thanks the link is helpful. It's just I was hoping to find a script that >> could care of most of the convert and just needed a little hand coding. >> -- >> John Fabiani >> >> ---(end of broadcast)--- >> TIP 3: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/docs/faq >> > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] does anyone have a tool to convert SP'sT-SQL to Postgres
Pavel is doing nice work on Orafce & the EnterpriseDB PG Community Fund is now sponsoring him to do more. On 2/10/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: Denis Lussier wrote: > Oracle provides a free tool for converting TSQL into PL/SQL. > > You can then use the PL/SQL on an EnterpriseDB database, if this doesn't > work for ya... PL/SQL is quite a bit closer to PLpgSQL than TSQL is so > you'll be well on your way. There is also the open source Orafce project that can help you with a lot of the oracle specific functions. You can find it on pgfoundry -- http://www.pgfoundry.org/ Joshua D. Drake > > --Luss > http://www.enterprisedb.com > > On 2/9/07, johnf <[EMAIL PROTECTED]> wrote: >> >> On Friday 09 February 2007 04:43, A. Kretschmer wrote: >> > am Thu, dem 08.02.2007, um 21:28:08 -0800 mailte johnf folgendes: >> > > Hi, >> > > I'm hoping someone has already taken the time to write a routine (in >> some >> > > language - python,perl, etc..) to convert MS SQL T-SQL stored >> procedures >> > > into Postgres PL/pgSQL. And of course they are willing to share. >> > >> > See there: http://www.postgresql.org/docs/techdocs.3 >> > >> > I hope, you can find what you need. >> > >> > >> > Andreas >> >> Thanks the link is helpful. It's just I was hoping to find a script that >> could care of most of the convert and just needed a little hand coding. >> -- >> John Fabiani >> >> ---(end of broadcast)--- >> TIP 3: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/docs/faq >> > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Re: [GENERAL] Priorities for users or queries?
There is a function pg_backend_pid() that will return the PID for the current session. You could call this from your updating app to get a pid to feed to the NICE command. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Benjamin Arai Sent: Saturday, February 10, 2007 6:56 PM To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org Subject: [GENERAL] Priorities for users or queries? Hi, Is there a way to give priorities to queries or users? Something similar to NICE in Linux. My goal is to give the updating (backend) application a very low priority and give the web application a high priority to avoid disturbing the user experience. Thanks in advance! Benjamin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [ADMIN] Priorities for users or queries?
Just to clarify, there is no way to throttle specific queries or users in PostgreSQL? Benjamin Joshua D. Drake wrote: Benjamin Arai wrote: Hi, Is there a way to give priorities to queries or users? Something similar to NICE in Linux. My goal is to give the updating (backend) application a very low priority and give the web application a high priority to avoid disturbing the user experience.\ Nope :) Joshua D. Drake Thanks in advance! Benjamin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [GENERAL] [ADMIN] Priorities for users or queries?
Benjamin Arai wrote: > Just to clarify, there is no way to throttle specific queries or users > in PostgreSQL? That is correct. Sincerely, Joshua D. Drake > > Benjamin > > Joshua D. Drake wrote: >> Benjamin Arai wrote: >> >>> Hi, >>> >>> Is there a way to give priorities to queries or users? Something >>> similar to NICE in Linux. My goal is to give the updating (backend) >>> application a very low priority and give the web application a high >>> priority to avoid disturbing the user experience.\ >>> >> >> Nope :) >> >> Joshua D. Drake >> >> >> >>> Thanks in advance! >>> >>> Benjamin >>> >>> >>> ---(end of broadcast)--- >>> TIP 7: You can help support the PostgreSQL project by donating at >>> >>>http://www.postgresql.org/about/donate >>> >>> >> >> >> > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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
