Re: [GENERAL] Updateable Views or Synonyms.
On 30 May 2012, at 1:16, Tim Uckun wrote: > I am wondering if either of these features are on the plate for > postgres anytime soon? I see conversations going back to 2007 on > updateable views and some conversations about synonyms but obviously > they have never been added to the database for some reason or another. You can create updateable views by adding INSERT, UPDATE and DELETE rules to them. Alban Hertroys -- The scale of a problem often equals the size of an ego. -- 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] How to handle nested record data.
On Wed, May 30, 2012 at 9:03 AM, yi huang wrote: > It turns out i also need to define a type for the result record of `foo`, > because record can't reveal the structure of the result (it complains: > record "r" has no field "somerow"). > I have to created this type: > > create type foo_result as (somerow SomeTable, otherinfo varchar); > > then change `r record;` to `r foo_result;` , no need change `foo` itself, > and it works now. > > I don't know is this the best way to do this though. I am not sure my assumption is correct but have not you tried to do it like this SELECT * FROM foo(seq) AS (somerow SomeTable, otherinfo varchar) it will probably help to avoid creation of a separate type for the function result. > >> >> >> regards >> >> Pavel >> >> 2012/5/30 yi huang : >> > I'm porting a oracle function to postgresql, which has signature like >> > this: >> > >> > FUNCTION foo >> > ( seq IN varchar >> > , somerow OUT SomeTable >> > , otherinfo OUT varchar >> > ) >> > >> > It's easy to port this function itself to postgresql, but i have problem >> > to >> > execute this function and assign the results into variables: >> > >> > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo); >> > >> > It complains v_somerow can not be row type. >> > >> > How to handle the result of function foo? >> > >> > Best regards. >> > YiHuang. > > > > > -- > http://yi-programmer.com/ -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- 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] Updateable Views or Synonyms.
On Wed, May 30, 2012 at 12:38 PM, Alban Hertroys wrote: > On 30 May 2012, at 1:16, Tim Uckun wrote: > > > I am wondering if either of these features are on the plate for > > postgres anytime soon? I see conversations going back to 2007 on > > updateable views and some conversations about synonyms but obviously > > they have never been added to the database for some reason or another. > > > You can also create a updateable VIEW using "INSTEAD OF" trigger which is a new feature in PG 9.1. http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] google F1
So, If you haven't heard the news yet, google is migrating off MySQL for adwords. They decided to implement their own system, F1: http://static.googleusercontent.com/external_content/untrusted_dlcp/research.google.com/en/us/pubs/archive/38125.pdf It's a distributed SQL system, but they opted not to implement 'eventual consistency' -- which I very much agree with. It's fully transactional (I think?) and trades transaction latency for the sharding capabilities. It's an interesting take: how does it compare to postgres? Does it truly scale? merlin -- 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] timestamps, formatting, and internals
On 05/29/2012 07:08 PM, Adrian Klaver wrote: On 05/29/2012 04:28 PM, David Salisbury wrote: On 5/27/12 12:25 AM, Jasen Betts wrote: The query: "show integer_datetimes;" should return 'on' which means timestamps are microsecond precision if it returns 'off' your database was built with floating point timstamps and equality tests will be unreliable, I find that rather interesting. I was told that I was losing microseconds when I extracted an epoch from the difference between two timestamps and casted that value to an integer. So if I have integer timestamps ( your case above ) I get microseconds, but integer epochs is without microseconds? Think I realize where the confusion is now. When Jasen mentioned integer datetimes he was referring to the internal storage format Postgres uses to record the datetime value. Via the magic of programming(others will have to fill that part in) the internal format can represent time down to microseconds even though the value is actually stored as an eight-byte integer. When you do an explicit cast of a timestamp value to integer you are asking that the value be only a whole number and the decimal portion is discarded. In other words the internal integer encodes the decimal values the external integer does not. Thanks, -ds -- Adrian Klaver adrian.kla...@gmail.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] Postgres no longer starts
CentOS 5.x (now 5.8), Postgres 8.4.something. Postgres had been up and running for over a year now. After an update on the system, and adding mod_ssl in Apache (is this related? No idea.), Postgres no longer starts up. It just fails silently. "pgstartup.log" contains only one single line: runuser: cannot set groups: Operation not permitted I have no idea where to begin to debug this; Google only produces a handful of results about this error message in relation to Postgres. None fix my problem. Whatever I do, everything just fails quietly. As far as I can see, the configuration files haven't changed. Help? Where do I even begin to debug this? Could a complete reinstall of Postgres and restoration of the databases help? -- Bart.
Re: [GENERAL] What's a correct or good Encoding for Postgres 9.1.2?
Hi Please assist, I am junior DBA. We are upgrading from postgres 7.3.4 where we were using SQL_ASCII Encoding to Postgres 9.1.2. It looks like Postgres 9.1.2 forces you to use UTF8 Encoding if I read from this link http://www.postgresql.org/docs/9.1/static/release-9-1-2.html . Can we still use SQL_ASCII in postgres 9.1.2? Your help will bemuch appreciated. Regards, Khangelani Gama CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] Postgres no longer starts
On Wed, May 30, 2012 at 8:55 PM, Bart Lateur wrote: > CentOS 5.x (now 5.8), Postgres 8.4.something. Postgres had been up and > running for over a year now. > > ** ** > > After an update on the system, and adding mod_ssl in Apache (is this > related? No idea.), Postgres no longer starts up. It just fails silently. > “pgstartup.log” contains only one single line: > > ** ** > > runuser: cannot set groups: Operation not permitted > > ** ** > > ** ** > > I have no idea where to begin to debug this; Google only produces a > handful of results about this error message in relation to Postgres. None fix > my problem. > > ** ** > > Whatever I do, everything just fails quietly. As far as I can see, the > configuration files haven’t changed. > > ** ** > > Help? Where do I even begin to debug this? Could a complete reinstall of > Postgres and restoration of the databases help? > > ** ** > > -- > > Bart. > > ** ** > > ** ** > Could you please, try to start the cluster using "pg_ctl" utility: Syntax: /bin/pg_ctl -D start Ex:- /opt/PostgreSQL/8.4/bin/pg_ctl -D /opt/PostgreSQL/8.4/data start and then share the log file information -- Thanks & Regards, Raghu Ram Skypeid: raghu.ramedb EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] What's a correct or good Encoding for Postgres 9.1.2?
On Wed, May 30, 2012 at 11:58 PM, Khangelani Gama wrote: > Hi > > > > Please assist, I am junior DBA. We are upgrading from postgres 7.3.4 where > we were using SQL_ASCII Encoding to Postgres 9.1.2. It looks like Postgres > 9.1.2 forces you to use UTF8 Encoding if I read from this link > http://www.postgresql.org/docs/9.1/static/release-9-1-2.html . Can we > still use SQL_ASCII in postgres 9.1.2? > > > > > > Your help will bemuch appreciated. > > > > > > Regards, > > Khangelani Gama > > > CONFIDENTIALITY NOTICE > The contents of and attachments to this e-mail are intended for the addressee > only, and may contain the confidential information of Argility (Proprietary) > Limited and/or its subsidiaries. Any review, use or dissemination thereof by > anyone other than the intended addressee is prohibited. > If you are not the intended addressee please notify the writer immediately > and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries > distance themselves from and accept no liability for unauthorised use of > their e-mail facilities or e-mails sent other than strictly for business > purposes. > > Hi UTF-8 should be compatible with SQL_ASCII.
Re: [GENERAL] What's a correct or good Encoding for Postgres 9.1.2?
Hi We were getting this error. ERROR: invalid byte sequence for encoding "UTF8": 0xa0 We are thinking of using SQL_ASCII in postgres 9.1.2 And that we will be feeding from backup server(UTF-8) into another server that’s using SQL_ASCII. Where are not if it’ll be fine to feed from UTF-8 to SQL_ASCII. Clear explaination: We have Primary server and the backup server running on postgres7.3.4 using SQL_ASCII. And another server which is already on 9.1.2 already is using SQL_ASCII. We feed the same data(from backup server) into this third server. Now we are upgrading these two servers(primary and backup) to Postgres9.1.2 where we use UTF-8 encoding. So talking about compatibility, you are saying we can continue using UTF-8?, but this will create more work for us because most of our scripts assume that encoding is SQL_ASCII hence we want continue using SQL_ASCII in Postgres 9.1.2. Thanks *From:* pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] *On Behalf Of *gelin yan *Sent:* Wednesday, May 30, 2012 6:59 PM *To:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] What's a correct or good Encoding for Postgres 9.1.2? On Wed, May 30, 2012 at 11:58 PM, Khangelani Gama wrote: Hi Please assist, I am junior DBA. We are upgrading from postgres 7.3.4 where we were using SQL_ASCII Encoding to Postgres 9.1.2. It looks like Postgres 9.1.2 forces you to use UTF8 Encoding if I read from this link http://www.postgresql.org/docs/9.1/static/release-9-1-2.html . Can we still use SQL_ASCII in postgres 9.1.2? Your help will bemuch appreciated. Regards, Khangelani Gama CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. Hi UTF-8 should be compatible with SQL_ASCII. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] Postgres no longer starts
On 05/30/12 8:25 AM, Bart Lateur wrote: After an update on the system, and adding mod_ssl in Apache (is this related? No idea.), Postgres no longer starts up. It just fails silently. “pgstartup.log” contains only one single line: runuser: cannot set groups: Operation not permitted did you use yum to install everything? do you use any non-standard CentOS repositories ? CentOS 5 came with an older version of PG (8.1, I think?), so where did you get 8.4 from, and how did you install it originally? the PGDG packaged postgresql versions for CentOS/RHEL appear to use `runuser` instead of `su` if selinux is enabled ... so this is likely a selinux related issue. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] What's a correct or good Encoding for Postgres 9.1.2?
On 05/30/12 10:17 AM, Khangelani Gama wrote: So talking about compatibility, you are saying we can continue using UTF-8?, but this will create more work for us because most of our scripts assume that encoding is SQL_ASCII hence we want continue using SQL_ASCII in Postgres 9.1.2. SQL_ASCII is not really an encoding, its saying "this data has no encoding at all, its just bytes". UTF-8 will reject any data thats not properly UTF8 encoded. converting from 'undefined' encoding to a rigorously enforced encoding is problematic. On the other hand, working in SQL_ASCII has all kinda ugly issues, like length(somestring) is just counting bytes, and not characters if the string happens to be a multibyte encoded entity. collation order is just binary. upper/lower don't work on anything other than USASCII (eg, accented characters are ignored). sounds to me like you're stuck in SQL_ASCII -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] What's a correct or good Encoding for Postgres 9.1.2?
Many Thanks for feedback. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Wednesday, May 30, 2012 7:54 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] What's a correct or good Encoding for Postgres 9.1.2? On 05/30/12 10:17 AM, Khangelani Gama wrote: > So talking about compatibility, you are saying we can continue using > UTF-8?, but this will create more work for us because most of our > scripts assume that encoding is SQL_ASCII hence we want continue > using SQL_ASCII in Postgres 9.1.2. SQL_ASCII is not really an encoding, its saying "this data has no encoding at all, its just bytes". UTF-8 will reject any data thats not properly UTF8 encoded. converting from 'undefined' encoding to a rigorously enforced encoding is problematic. On the other hand, working in SQL_ASCII has all kinda ugly issues, like length(somestring) is just counting bytes, and not characters if the string happens to be a multibyte encoded entity. collation order is just binary. upper/lower don't work on anything other than USASCII (eg, accented characters are ignored). sounds to me like you're stuck in SQL_ASCII -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.2 Range Types
Does 9.2 support an array of ranges? For example, I have the following int4ranges => [0,5999) and [7000, 7999) which needs to be associated with the same record. Thanks, Dave
Re: [GENERAL] Postgres no longer starts
(Sorry if message threading is a bit off; I'm replying from a different mail account as the previous post, so the "ref" header won't match.) Well I tried /usr/bin/pg_ctl -D /var/lib/pgsql/data start -l /tmp/pglogfile and it just says server starting and then... nothing. It seems to quit immediately. The logfile, /tmp/logfile exists, but is empty. (I noticed the previous logfile, "pgstartup.log", was still the same and hadn't changed since a few hours earlier, so the "modified" timestamp confirmed, so it wasn't being updated any more.) As where I did get the installer of Postgres for CentOS: I don't quite remember the details, as it has been running well for several months. But I got the idea from one of the many blog posts about it, such as these: http://blog.lystor.org.ua/2010/05/upgrading-postgresql-81-to-84-centos-55.html http://www.if-not-true-then-false.com/2010/howto-install-postgresql-8-4-database-server-on-centos-fedora-red-hat/ I think it just comes from one of the unofficial CentOS repositories. Like I said, it has been running well for months and the latest upgrade was unrelated to PostGres. It may have been some manual tweak I did a few weeks ago... PostGres hadn't been restarted since then. If I knew what it was complaining about, I might be able to fix it. I have already made a backup of the /var/lib/pgsql/data subdirectory, I might just remove all of Postgres, and then reinstall. Luckily this is a development machine, but as we don't know what causes the problem we fear we might one day face the exact same problem where it does matter: on a production machine. So we'd like to know exactly what went wrong... -- Bart Lateur bart.lat...@telenet.be -- 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] 9.2 Range Types
On Wed, May 30, 2012 at 1:59 PM, Dave Bettin wrote:
> Does 9.2 support an array of ranges? For example, I have the following
> int4ranges => [0,5999) and [7000, 7999) which needs to be associated with
> the same record.
yup:
postgres=# select array['[0,5999)'::int4range];
array
--
{"[0,5999)"}
(1 row)
merlin
--
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] Postgres no longer starts
Bart: Failing a more definitive diagnostic approach, I suggest that you post your entire pgstartup.log rather than just the error message. My guess is that the position in that log where the error occurs will give folks who are more familiar with the startup sequence a reasonable idea of where in the start process things failed and may at least help you to localize your search for problems. Good luck, John -- 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] 9.2 Range Types
In theory...(not running 9.2 that I could test):
{"[0,5999)","[7000,7999)"}::int4range[]
Not sure the exact type name for the int4range. The double quotes are
necessary. The curly-braces surround the whole array and elements are
separated by commas.
David J.
On May 30, 2012, at 14:59, Dave Bettin wrote:
> Does 9.2 support an array of ranges? For example, I have the following
> int4ranges => [0,5999) and [7000, 7999) which needs to be associated with the
> same record.
>
> Thanks,
> Dave
--
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] Postgres no longer starts
On Wednesday, May 30, 2012 08:22:58 PM Bart Lateur wrote: > Luckily this is a development machine, but as we don't know what causes > the problem we fear we might one day face the exact same problem where > it does matter: on a production machine. So we'd like to know exactly > what went wrong.. Change selinux to permissive instead of enforcing and see if PostgreSQL then runs. If it does, you can look at the selinux logging to see what would have been denied in enforcing mode, and hopefully fix it from there. -- 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] Postgres no longer starts
Alan Hodgson writes: > On Wednesday, May 30, 2012 08:22:58 PM Bart Lateur wrote: >> Luckily this is a development machine, but as we don't know what causes >> the problem we fear we might one day face the exact same problem where >> it does matter: on a production machine. So we'd like to know exactly >> what went wrong.. > Change selinux to permissive instead of enforcing and see if PostgreSQL then > runs. If it does, you can look at the selinux logging to see what would have > been denied in enforcing mode, and hopefully fix it from there. Yeah, I concur that this smells like a selinux issue. Most likely, the software update you did messed up the selinux "context" settings for some files. restorecon should be able to fix it for you, if so. As Alan says, the kernel log (or separate avc log, depending on how your system is set up) should show evidence of the problem if this is where it is. regards, tom lane -- 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] timestamps, formatting, and internals
On 5/30/12 9:42 AM, Adrian Klaver wrote: Think I realize where the confusion is now. When Jasen mentioned integer datetimes he was referring to the internal storage format Postgres uses to record the datetime value. Via the magic of programming(others will have to fill that part in) the internal format can represent time down to microseconds even though the value is actually stored as an eight-byte integer. When you do an explicit cast of a timestamp value to integer you are asking that the value be only a whole number and the decimal portion is discarded. In other words the internal integer encodes the decimal values the external integer does not. Thanks! I was looking for some sort of verification along these lines. So in my mind, the internal storage of a timestamp would be the number of milliseconds since 1970 ( or similar ). But to me, if I cast something that is an integer into an integer it would still be an integer ;) , and still hold the milliseconds. Perhaps if I cast a datetime into a bigint it'll still hold the number of ms? Some sort of parameter setting for dates would be nice to be able to default a date/time format down to the ms, w/o having to explicitly format it with every select... imho. -ds -- 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] Picksplit warning
On Tue, 2012-05-08 at 15:11 +0300, Oleg Mürk wrote: > Hello, > > Our postgresql logs are getting filled with warnings: > LOG: picksplit method for column COLUMN_IDX of index INDEX_NAME > doesn't support secondary split > We are using gist indexes on integer, timestamp, and Postgis geometry. > > Is there a way to work around this problem? http://archives.postgresql.org/pgsql-general/2007-08/msg01810.php A similar complaint from a long time ago. It looks like the conclusion was to demote that to a DEBUG1 message, which won't clutter your logs. It doesn't indicate a real problem. It's essentially saying that PostGIS is missing out on a potential optimization, which is not something you can easily fix. It's also not very well documented, unfortunately, so it's not something the PostGIS folks can fix easily, either. I brought this up on -hackers, so hopefully it will be resolved. In the meantime, you're stuck with the messages cluttering your logfiles. Regards, Jeff Davis -- 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] timestamps, formatting, and internals
On 05/30/2012 01:48 PM, David Salisbury wrote: On 5/30/12 9:42 AM, Adrian Klaver wrote: Think I realize where the confusion is now. When Jasen mentioned integer datetimes he was referring to the internal storage format Postgres uses to record the datetime value. Via the magic of programming(others will have to fill that part in) the internal format can represent time down to microseconds even though the value is actually stored as an eight-byte integer. When you do an explicit cast of a timestamp value to integer you are asking that the value be only a whole number and the decimal portion is discarded. In other words the internal integer encodes the decimal values the external integer does not. Thanks! I was looking for some sort of verification along these lines. So in my mind, the internal storage of a timestamp would be the number of milliseconds since 1970 ( or similar ). But to me, if I cast something that is an integer into an integer it would still be an integer ;) , and still hold the milliseconds. Perhaps if I cast a datetime into a bigint it'll still hold the number of ms? Some sort of parameter setting for dates would be nice to be able to default a date/time format down to the ms, w/o having to explicitly format it with every select... imho The client does not see the internal value. That value is decoded when presented to the client and encoded when the client presents a datetime value. Any integer casting you do as the client will not change that. As to datetime formatting, I believe that was covered in another thread:) test=> SELECT now(); now --- 2012-05-30 14:25:28.719475-07 (1 row) test=> SELECT '2012-05-30'::TIMESTAMP; timestamp - 2012-05-30 00:00:00 (1 row) test=> SELECT '2012-05-30 2:26:45.56789'::TIMESTAMP; timestamp --- 2012-05-30 02:26:45.56789 (1 row) A timestamp will return values that are significant. -ds -- Adrian Klaver adrian.kla...@gmail.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] Streaming Replication Error
On Mon, 2012-04-30 at 17:23 -0400, Andrew Hannon wrote: > 1. Is our data intact? PG eventually starts up, and it seems like once > the streaming suffers the FATAL error, it falls back to performing log > restores. I don't see anything alarming there. Postgres will not start up if it thinks it's really missing data. I'd advise using an archive command that does not output anything unless it's something you really need to know. A log file missing from the archive is normal operation for recovery mode, so notices telling you that are just cluttering the log. > 2. What triggers this error? Too much time between log recovery, > streaming startup and a low wal_keep_segments value (currently 128)? 128 sounds like a high-enough number, so after it catches up fully, it should be plenty. It looks like, while trying to catch up, it falls within the 128 segments and begins streaming, and then momentarily falls back out and needs to restore from the archive. Unless you have steady-state replication lag, it should catch up fully and then just be able to use streaming all the time. Do you see it resume streaming later on in the logfile? Disclaimer: I'm not 100% confident in my response, so please take it with a grain of salt, but I hope it is helpful anyway. Regards, Jeff Davis -- 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] Export and import from one postgres server to another
On Wed, 2012-05-30 at 09:56 +0800, Craig Ringer wrote: > On 05/29/2012 02:27 AM, Alexander Reichstadt wrote: > > Hi, > > > > I am trying to "simply" export my postgres database from one server and > > then import it into another. I thought I could use PhpPgAdmin, but the > > hints on the web don't seem to work. One said to simply get a dump from one > > phpPgAdmin, the go to the other server, select the database, click the SQL > > button and then select the dump file. When I do this the import fails > > because the dump file begins with a \connect statement. > > I don't really know why pg_dump includes that \connect , but it isn't > the only problem you'll hit with that approach. PgAdmin-III seems to > wrap the invoked SQL in a transaction, and the dump will try to > (re)-create the `postgres' role, which will fail and abort the transaction. > While I know the thread isn't on pgAdmin, I still want to fix an error. pgAdmin doesn't wrap the SQL in a transaction (in the query tool, which is the only tool that allows you to run a query). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] 9.2 Range Types
Awesome, I will give it a try.
Thanks!
On Wed, May 30, 2012 at 1:00 PM, David Johnston wrote:
> In theory...(not running 9.2 that I could test):
>
> {"[0,5999)","[7000,7999)"}::int4range[]
>
> Not sure the exact type name for the int4range. The double quotes are
> necessary. The curly-braces surround the whole array and elements are
> separated by commas.
>
> David J.
>
> On May 30, 2012, at 14:59, Dave Bettin wrote:
>
> > Does 9.2 support an array of ranges? For example, I have the following
> int4ranges => [0,5999) and [7000, 7999) which needs to be associated with
> the same record.
> >
> > Thanks,
> > Dave
>
Re: [GENERAL] Usability write-up - looking at Pg, especially PgAdmin-III and Pg on Windows, from an inexperienced user PoV
Hi, On Fri, 2012-05-25 at 17:56 +0800, Craig Ringer wrote: > Hi all > > I just had the ... pleasure ... of using Windows with Pg again and was > in a usability review frame of mind. I landed up trying to restore my > database using PgAdmin-III, and was astonished at how difficult and > painful it was. The issues weren't all PgAdmin-III either, there are a > few Pg-on-Windows issues and a few plain warts in terms of PostgreSQL > usability in general. > > It felt like my first experience with Oracle (ie: screaming, pain and > confusion) not the smooth and pleasurable experience I've come to be so > used to with Pg. > > I was sufficiently surprised by some of the issues that I've written up > a post on the matter. I intended it to be a few usability notes, though > it's turned into a bit more than that. I think it's really imporant to > highlight these issues, because if this had been my first experience > with PostgreSQL I would have walked away and never, ever, ever come back. > > It might be premature to post this before I've reviewed and re-edited > the post, but hey, a few flames won't hurt. I'm trying to be > constructive in the following, just also trying to express the > frustration that someone new to Pg would experience. > > http://blog.ringerc.id.au/2012/05/postgresql-usability-pgadmin-iii-and-pg.html > Didn't yet read the blog post but will do later. > Brief summary of pain points: > > - The need to back up globals separately even when using a custom-format > dump is a giant wart. GIANT. The need to restore them using a completely > separate procedure because you can't use pg_restore just makes it even > uglier. > +1 I would love to see new pg_dump options that will add the CREATE ROLE and CREATE TABLESPACE statements needed to restore a specific database dump. > - Encoding/locale name mismatches between Windows and Linux are really > unpleasant, esp when they prevent the restore of backups w/o > workarounds! Big, big wart here. > Kinda agree, but don't see a really good solution here. > - The "restore" dialog in PgAdmin-III needs a lot of love. +1 > Harder to access than it should be I guess you mean that you want the restore option at the server level. Could be a nice idea, I see how we could do that. It would also allow us to add an option to create the database, just before restoring the dump. > , some things very counter-intuitive Like? > , blocks UI while restore in progress Shouldn't do so. Of course, if you restore your dump on the same computer, you may experience some extreme latency that will make you think you're blocked. > , can't cope with "backups" that're really SQL scripts at all Yes, we don't provide psql that would allow us to restore SQL scripts. I would make the distribution package bigger, and we don't think it would be good. > . No interpretation of error codes is offered Kinda hard when you're running an command line tool to read and interpret every error. > , which is > particularly important because 0-is-success isn't obvious to most > people. Sure. > Messages windows aren't updated until the command completes. It should work (and it works for VACUUM IIRC) but for some reasons, unknown to me, it doesn't with this specific window. > The cancel button stays enabled after the command finishes and should be > "Abort" not "Cancel" anyway. No interpretation or hints gets presented > for common errors. It doesn't exclude mutually exclusive options. Etc > etc. Again, see post. > > - Running a script from PgAdmin-III is way, way too hard. You only have to open the query tool. Doesn't seem that hard to me. The only thing the query tool cannot do is COPY from stdin, or to stdout. Which is annoying because you cannot restore a SQL dump this way. > In fact, as > far as I can tell, you just can't invoke psql with a script w/o > bypassing PgAdmin-III and using the cmdline. That's right. Find me a good UI for that, and I may be able to do it. > Obvious on Linux, but who > wants to use a Windows command line? > > - The PgAdmin-III editor can't stream a file to a Pg backend, it has to > load the whole lot into RAM Yes. That's a good thing and a bad thing. To be able to execute a SQL file, you need to load it on the query tool. That helps you to review it, and if the execution goes wrong, it shows you where the first error is. And if you want to execute a really big SQL file, pgAdmin is right now simply the wrong tool. We may had a menu item to select a file and execute it on a database. Not sure lots of people will find it useful. > , and it doesn't understand psql syntax even > enough to say "whoops, \connect is only supported by psql, did you mean > to run this as a script in psql?". About psql metacommands, the query tool doesn't know them, and won't know them, unless somone wants to work on this. I simply won't. But I like the idea of telling the user "...only supported by psql...". That would be a good t
Re: [GENERAL] Usability write-up - looking at Pg, especially PgAdmin-III and Pg on Windows, from an inexperienced user PoV
On Fri, 2012-05-25 at 19:15 +0800, Craig Ringer wrote: > On 25/05/2012 6:55 PM, Marti Raudsepp wrote: > > Together with an earlier study about common PostgreSQL pitfalls, I've > > created an article on the wiki: > > https://wiki.postgresql.org/wiki/Usability_reviews > > > > However, I suspect posting long rants like this won't get us very far. > > Instead, I think these problems should be posted one by one, with > > proposed solutions, so they can be discussed in detail without > > distractions. Once there seems to be a consensus for a solution, add a > > TODO list item -- I suspect most of these items are quite easy to > > solve in code, once it's clear *how* to address them. And the TODO > > list has been short of simple items recently. > > You're quite right. FWIW, it wasn't intended to be a long rant - I > started out writing a point-by-point review of a few usability issues - > though it certainly turned into a long rant as I started seeing more and > more issues. > And there are certainly more issues than the ones you talked about. I'm really interested in knowing them, to be able to fix them. > It's my intention to break that down into specific problem areas and > points, I just thought it was worth getting a few initial impressions too. > Not sure it's the best way because it's difficult, if not impossible, to answer some points (for example "some things very counter-intuitive"). Anyway, please, start some threads on pgadmin-hackers, so that we can start fixing issues, and bugs you've found. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] Change request - log line prefix
I am part of a team that fills an operational roll administering 1000+ servers and 100's of applications. Of course we need to "read" all of our logs, and must use computers to help us. In filtering postgreSQL logs there is one thing that makes life difficult for us admins. Nice things about the postgreSQL logs - user definable prefix - each log line after the prefix contains a log line status such as; ERROR: FATAL: LOG: NOTICE: WARNING: STATEMENT: - the configurable compile time option to set the wrap column for the log lines. Now for the bad things Even when the wrap column is set to a very large value (32k) STATEMENT lines still wrap according to the line breaks in the original SQL statement. Wrapped line no longer have the prefix - difficult to grep the log for everything pertaining to a particular database or user Wrapped lines no longer have the log line status - difficult to auto-ignore all NOTICE status log lines when they wrap, or ignore all user STATEMENT lines because they almost always wrap. In conclusion, I would like to see a logging change that included the prefix on EVERY line, and included the STATUS on every line. Comments? If everyone :-) is in agreement can the authors just "get it done"? Thanks for your time. Evan Rempel Systems administrator University of Victoria. -- 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] Change request - log line prefix
Evan Rempel writes: > Even when the wrap column is set to a very large value (32k) STATEMENT lines > still wrap according to the line breaks in > the original SQL statement. > Wrapped line no longer have the prefix - difficult to grep the log for > everything pertaining to a particular database or user > Wrapped lines no longer have the log line status - difficult to auto-ignore > all NOTICE status log lines when they wrap, or > ignore all user STATEMENT lines because they almost always wrap. I think your life would be better if you used CSV log format. > In conclusion, I would like to see a logging change that included the prefix > on EVERY line, and included the STATUS on every line. This doesn't really sound like an improvement to me. It's going to make the logs bulkier, but they're still not automatically parseable in any meaningful sense. CSV is the way to go if you want machine-readable logs. regards, tom lane -- 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] Change request - log line prefix
On Thu, May 31, 2012 at 2:05 PM, Evan Rempel wrote: > Even when the wrap column is set to a very large value (32k) STATEMENT lines > still wrap according to the line breaks in > the original SQL statement. The problem isn't so much the wrapping, it seems, as that your statements' line breaks are being propagated through. So as a possible alternative solution, perhaps there could be an option to replace newlines with spaces before the line goes to the log? ChrisA -- 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] Change request - log line prefix
Can this be done to syslog destination? Evan Rempel Systems Administrator University of Victoria On 2012-05-30, at 10:37 PM, "Tom Lane" wrote: > Evan Rempel writes: >> Even when the wrap column is set to a very large value (32k) STATEMENT lines >> still wrap according to the line breaks in >> the original SQL statement. >> Wrapped line no longer have the prefix - difficult to grep the log for >> everything pertaining to a particular database or user >> Wrapped lines no longer have the log line status - difficult to auto-ignore >> all NOTICE status log lines when they wrap, or >> ignore all user STATEMENT lines because they almost always wrap. > > I think your life would be better if you used CSV log format. > >> In conclusion, I would like to see a logging change that included the prefix >> on EVERY line, and included the STATUS on every line. > > This doesn't really sound like an improvement to me. It's going to make > the logs bulkier, but they're still not automatically parseable in any > meaningful sense. CSV is the way to go if you want machine-readable logs. > >regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
