Re: [GENERAL] Design ? table vs. view?
Hey, I guess you know all about PL/R, the R language extension for postgres . It is very convenient, though be carefull as sometime it crashed my server. Cheers, Rémi-C 2014-07-16 3:42 GMT+02:00 John McKown john.archie.mck...@gmail.com: On Tue, Jul 15, 2014 at 8:46 AM, David G Johnston david.g.johns...@gmail.com wrote: John McKown wrote I have a table which has some raw data in it. By raw, I mean it is minimally processed from a log file. Every week, I update this table by processing the weekly log using awk to create a psql script file which looks similar to: snip So the overhead may be quite high, because to SELECT from RUNINFO, PostgreSQL must realize all four views. I appreciate your thoughts on if this is OK, given that performance is currently acceptable. Mainly because this work is basically only done one a week, on Sundays. And I don't do it myself, it is done via a scheduler (not cron, but similar) which runs some scripts. I would likely make jobrun.runinfo into a table while leaving jobrun.rawdata as-is. I would have a function that populates runinfo from rawdata that I would call after performing the copy to rawdata. There would be no views - unless you desire a view interface over runinfo for API or permission reasons. In 9.4 you can (probably) make runinfo an explicit MATERIALIZED VIEW and perform REFRESH command to accomplish the same thing - though I am not particularly familiar with the mechanics of that feature. David J. Being the indecisive nut that I am, I am going to do both grin/. I will keep the current view. But when I update the rawdata, what I will then do is: drop table runinfo_table; create table runinfo_table as select distinct * from runinfo; I am fairly confident that there cannot be any duplicates in runinfo. But, being paranoid as well, I will do the DISTINCT just to be sure. I may change the VIEW to do that in the future, and remove it from the preceeding. Since the process which updates the rawdata table is automated and runs on a Sunday, the time needed to recreate runinfo_table is not relevant to me. So I get what I want, unless I update rawdata off schedule. I cannot imagine why I would do that since the logs from which I create it are generally only available after 17:00 local time on Sunday. Getting the iogs-to-date information for the time since the last dump is basically a PITA and my current use is not critical. Actually, it is more a skunkworks project of my own to produce a set of nice graphs, using R, which _might_ turn out to be interesting to management, but the production of which _will_ help me learn PostgreSQL and R better (hopefully). Many thanks. -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown
Re: [GENERAL] php password authentication failed for user ...
Yes all clusters, run on the same machine. All packages are from pgdg. libpq5 is version 9.3.4-1.pgdg70+1 the problem is still the same, The relevant database is only for testing and development, I will downgrade to 9.1. Thanks for helping. Regards, basti Am 15.07.2014 16:46, schrieb Adrian Klaver: On 07/15/2014 07:17 AM, basti wrote: Hello Adrian, Yes I use the correct cluster. password authentication failed for user testuser ... is a line from the postgres log Yes I know what trust mean. psql in version 9.3 can connect without error, psql in version 9.1 connect to server 9.3 get the same error as above. I have done the setup. And I don't use ADO for this test. I have also test Postgres 9.1 and Postgres 9.4 both work with auth-method md5, except postgres 9.3 discuss there. To follow up on my previous post. Are all these clusters running on the same machine? Where they all installed from the same source, pgdg, or are they from different sources? I have to go, so I will get back to this later, though I suspect wiser minds then mine will get you an answer in the interim. It takes a round 5 hours to find this error. If anybody has the same problem be warned. Regards, basti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] php password authentication failed for user ...
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of basti Sent: Mittwoch, 16. Juli 2014 10:14 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] php password authentication failed for user ... Yes all clusters, run on the same machine. All packages are from pgdg. libpq5 is version 9.3.4-1.pgdg70+1 the problem is still the same, The relevant database is only for testing and development, I will downgrade to 9.1. Hello, Did you already try to connect using the IP instead of localhost ? regards, Marc Mamin Thanks for helping. Regards, basti Am 15.07.2014 16:46, schrieb Adrian Klaver: On 07/15/2014 07:17 AM, basti wrote: Hello Adrian, Yes I use the correct cluster. password authentication failed for user testuser ... is a line from the postgres log Yes I know what trust mean. psql in version 9.3 can connect without error, psql in version 9.1 connect to server 9.3 get the same error as above. I have done the setup. And I don't use ADO for this test. I have also test Postgres 9.1 and Postgres 9.4 both work with auth-method md5, except postgres 9.3 discuss there. To follow up on my previous post. Are all these clusters running on the same machine? Where they all installed from the same source, pgdg, or are they from different sources? I have to go, so I will get back to this later, though I suspect wiser minds then mine will get you an answer in the interim. It takes a round 5 hours to find this error. If anybody has the same problem be warned. Regards, basti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
Hey, We performed an upgrade via pg_upgrade from 9.1 to 9.3 a while back, and I'm almost certain we were bitten by this bug: http://www.postgresql.org/message-id/20140530121631.ge25...@alap3.anarazel.de Finding the discussion is nice... but what do we do to fix this? I read through the discussion, and it *seems* we can delete the file and restart since it's only an 8k file and we haven't gone far enough to wrap into a new file. Will that actually work, though? Or is it too late, since something has already requested that invalid transaction? What do we do? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
On 07/16/2014 07:08 AM, Shaun Thomas wrote: Hey, We performed an upgrade via pg_upgrade from 9.1 to 9.3 a while back, and I'm almost certain we were bitten by this bug: http://www.postgresql.org/message-id/20140530121631.ge25...@alap3.anarazel.de Finding the discussion is nice... but what do we do to fix this? I read through the discussion, and it *seems* we can delete the file and restart since it's only an 8k file and we haven't gone far enough to wrap into a new file. Will that actually work, though? Or is it too late, since something has already requested that invalid transaction? What do we do? See here: https://wiki.postgresql.org/wiki/20140702pg_upgrade_fix -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
See here: https://wiki.postgresql.org/wiki/20140702pg_upgrade_fix Thank $Deity! We'll schedule this ASAP. Our cluster is whining about this about every 10 seconds, which is kind of a PITA. Thanks again! __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] php password authentication failed for user ...
On 07/16/2014 01:13 AM, basti wrote: Yes all clusters, run on the same machine. All packages are from pgdg. libpq5 is version 9.3.4-1.pgdg70+1 That is not what a previous post indicated: dpkg -l | grep libpq ii libpq5 9.1.13-0wheezy1 amd64PostgreSQL C client library the problem is still the same, The relevant database is only for testing and development, I will downgrade to 9.1. I have a feeling that is not going to solve what I think is the underlying issue, you have a mixed source install of packages. Thanks for helping. Regards, basti -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
On 2014-07-16 14:24:12 +, Shaun Thomas wrote: See here: https://wiki.postgresql.org/wiki/20140702pg_upgrade_fix Thank $Deity! We'll schedule this ASAP. Our cluster is whining about this about every 10 seconds, which is kind of a PITA. What exactly is it whining about? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
This: 23334|429396427453c68b69.5b26|2014-07-16 09:25:45 CDT|ERROR: could not access status of transaction 7150346 23334|429396427453c68b69.5b26|2014-07-16 09:25:45 CDT|DETAIL: Could not open file pg_multixact/offsets/006D: No such file or directory. Been doing it every ten seconds since yesterday. My log line prefix is this: log_line_prefix = '%p|%x|%u|%d|%r|%c|%t|' So the user, database, and connection source are all blank. The PID is different every time, too. Not sure what to make of that. __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
Shaun Thomas wrote: This: 23334|429396427453c68b69.5b26|2014-07-16 09:25:45 CDT|ERROR: could not access status of transaction 7150346 23334|429396427453c68b69.5b26|2014-07-16 09:25:45 CDT|DETAIL: Could not open file pg_multixact/offsets/006D: No such file or directory. Been doing it every ten seconds since yesterday. My log line prefix is this: log_line_prefix = '%p|%x|%u|%d|%r|%c|%t|' So the user, database, and connection source are all blank. The PID is different every time, too. Not sure what to make of that. It's an autovacuum worker, which is expected. Just get rid of the file and all should be well. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
It's an autovacuum worker, which is expected. Just get rid of the file and all should be well. That's what I figured, but I didn't want to make assumptions. Does removing the file require a restart? __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
Alvaro Herrera wrote: Shaun Thomas wrote: This: 23334|429396427453c68b69.5b26|2014-07-16 09:25:45 CDT|ERROR: could not access status of transaction 7150346 23334|429396427453c68b69.5b26|2014-07-16 09:25:45 CDT|DETAIL: Could not open file pg_multixact/offsets/006D: No such file or directory. Been doing it every ten seconds since yesterday. My log line prefix is this: log_line_prefix = '%p|%x|%u|%d|%r|%c|%t|' So the user, database, and connection source are all blank. The PID is different every time, too. Not sure what to make of that. It's an autovacuum worker, which is expected. Just get rid of the file and all should be well. BTW if you do a hexdump of the file, it should be all zeroes. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
BTW if you do a hexdump of the file, it should be all zeroes. Yep, that's what I get. :) __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] We've been affected by a pg_upgrade bug. What do we do next?
Shaun Thomas wrote: It's an autovacuum worker, which is expected. Just get rid of the file and all should be well. That's what I figured, but I didn't want to make assumptions. Does removing the file require a restart? Don't think so, but TBH I didn't try. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why pg_toast table not get auto vacuumed?
Hi, PostgreSQL version: 9.1.16. Linux: RHEL6. After a heavy traffic run, a huge pg_toast table is seen. Its size is ~3G. There also exist other pg_toast tables in a much smaller size, for example, ~100M. The problem is that that huge pg_toast table never gets auto vacuumed while other small pg_toast tables do get auto vacuumed, as shown in pg logs. If running traffic again, the huge pg_toast table grows again. We have another system with the same setup but we only run light traffic on it. The pg_toast table from the same parent table also exists. But this pg_toast table only has a size ~50M and it gets auto vacuumed from time to time as shown in pg_logs. Why does not the huge pg_toast table in the first case get auto vacuumed? Are there anything we can do? Thanks, JL -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-pg-toast-table-not-get-auto-vacuumed-tp5811788.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Obsolete ToDo Item?
Hi, I was reading trough the ToDo list in the Postgres Wiki and noticed that one item for Fsync[1] seems to be obsolete: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options and whether fsync does anything Ideally this requires a separate test program like /contrib/pg_test_fsync that can be run at initdb time or optionally later. This was added with 9.1[2] , so I guess that item can be removed from the Wiki page? Regards Thomas [1] https://wiki.postgresql.org/wiki/Todo#Fsync [2] http://www.postgresql.org/docs/9.1/static/pgtestfsync.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Obsolete ToDo Item?
On Wed, Jul 16, 2014 at 1:21 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Hi, I was reading trough the ToDo list in the Postgres Wiki and noticed that one item for Fsync[1] seems to be obsolete: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options and whether fsync does anything Ideally this requires a separate test program like /contrib/pg_test_fsync that can be run at initdb time or optionally later. This was added with 9.1[2] , so I guess that item can be removed from the Wiki page? The to-do item is not for pg_test_fsync to be created, but rather for initdb to automatically invoke pg_test_fsync and then analyze the results and use that analysis to set up the fsync method. But I do think this entry is obsolete. If a certain method is too fast, is that because the operating system lies and is not really syncing the data, or is it because you have a BBU/NVRAM in front of the disk? Or because you aren't using spinning disk at all? I don't think that there is any way for initdb to safely figure out what is going on just based on the outcome of some timing tests. The person who purchased the hardware has to apply their knowledge. Cheers, Jeff
Re: [GENERAL] Quering complete PLPGSQL code
What's really strange is that the function is returning the code like this: FUNCTION public.PRUEBA_FUNCION2(p_1 integer, OUT p_2 integer, INOUT p_3 integer, VARIADIC p_4 integer[]) RETURNS SETOF record LANGUAGE plpgsql IMMUTABLE STRICT SECURITY DEFINER ROWS 200 AS $function$begin null; end;$function$ So the LANGUAGE, INMUTABLE, STRICT, options are written before the code instead of after the code. Regards, Néstor On Mon, Jul 14, 2014 at 8:07 PM, Néstor Boscán nesto...@gmail.com wrote: Thanks a lot that worked!!! On Mon, Jul 14, 2014 at 6:53 PM, Jerry Sievers gsiever...@comcast.net wrote: Néstor Boscán nesto...@gmail.com writes: Hi I want to get the PLPGSQL code from the PostgreSQL 9.1 database. I've used pg_proc that only gives me the body of the code. Is there a Postgres function that can build all the code? pg_get_functiondef(oid) Regards, NÊstor -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: [GENERAL] php password authentication failed for user ...
psql in version 9.1 connect to server 9.3 get the same error as above. = dpkg -l | grep libpq ii libpq5 9.1.13-0wheezy1 amd64PostgreSQL C client library the other I cant say, now thats ii libpq5:amd64 9.4~beta1-2.pgdg70+1 amd64PostgreSQL C client library Am 15.07.2014 16:33, schrieb Adrian Klaver: On 07/15/2014 07:17 AM, basti wrote: Hello Adrian, Yes I use the correct cluster. password authentication failed for user testuser ... is a line from the postgres log Yes I know what trust mean. psql in version 9.3 can connect without error, psql in version 9.1 connect to server 9.3 get the same error as above. That would seem to indicate that you have a version issue with libpq. What does dkpg show for installed versions of libpq? I have done the setup. And I don't use ADO for this test. I have also test Postgres 9.1 and Postgres 9.4 both work with auth-method md5, except postgres 9.3 discuss there. It takes a round 5 hours to find this error. What exactly does that mean? If anybody has the same problem be warned. Regards, basti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why would I need to explicitly cast a string literal to text?
As an exercise, I've written the following query to implement [FizzBuzz][1].
SELECT COALESCE(fizz || buzz, fizz, buzz, '' || n) AS fizzbuzz
FROM (
SELECT n0 + 3 * n3 + 9 * n9 + 27 * n27 + 81 * n81 AS n
FROM
(SELECT 0 AS n0 UNION ALL SELECT 1 UNION ALL SELECT 2
AS n0) AS N0,
(SELECT 0 AS n3 UNION ALL SELECT 1 UNION ALL SELECT 2
AS n3) AS N3,
(SELECT 0 AS n9 UNION ALL SELECT 1 UNION ALL SELECT 2
AS n9) AS N9,
(SELECT 0 AS n27 UNION ALL SELECT 1 UNION ALL SELECT 2
AS n27) AS N27,
(SELECT 0 AS n81 UNION ALL SELECT 1
AS n81) AS N81
) AS N
LEFT OUTER JOIN
(SELECT 3 AS fizzstep, CAST('Fizz' AS CHAR(4)) AS fizz) AS Fizz
ON n % fizzstep = 0
LEFT OUTER JOIN
(SELECT 5 AS buzzstep, CAST('Buzz' AS CHAR(4)) AS buzz) AS Buzz
ON n % buzzstep = 0
WHERE n BETWEEN 1 AND 100
ORDER BY n;
I realize that it could be vastly simplified using GENERATE_SERIES(), but I'm
aiming for the solution to be portable to SQLite 2, SQLite 3, and MySQL as well.
I'd like to know, why are the two explicit casts necessary? Casting to VARCHAR
or to TEXT also works. However, if I omit the casts, I get…
ERROR: failed to find conversion function from unknown to text: …
I would expect that PostgreSQL should be able to infer that the fizz and buzz
columns were some kind of text. (What else could they be?) It seems like a
design flaw to require a literal string to be cast to text, right?
[1]: http://en.wikipedia.org/wiki/Fizz_buzz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
