Re: [GENERAL] How to include Tablefunc as an extension
Stefan Schwarzer writes: > I do as indicated in Kyngchaos ReadMe file: > export PATH="/usr/local/pgsql-9.1/bin:$PATH" > export USE_PGXS=1 > make > sudo make install I'm beginning to wonder who are Kyngchaos and whether they are competent at all. They obviously didn't test the above advice. It does not work because sudo clears environment variables (at least for me on OS X Lion). You could possibly do this instead: sudo make USE_PGXS=1 install It would still be running without the custom PATH setting, but likely you don't need that for the install step. > Problem is that when I run the install, it shows this: > Makefile:19: ../../src/Makefile.global: No such file or directory > Makefile:20: /contrib/contrib-global.mk: No such file or directory > make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. > And this, after looking around, seem to indicate that I need to > configure the Postgres source code. No, if you got through the "make" part, you have configure info. It's just failing to propagate to the make install inside sudo. 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] How to include Tablefunc as an extension
>> I do as indicated in Kyngchaos ReadMe file: > >>export PATH="/usr/local/pgsql-9.1/bin:$PATH" >>export USE_PGXS=1 >>make >>sudo make install > > I'm beginning to wonder who are Kyngchaos and whether they are competent > at all. They obviously didn't test the above advice. It does not work > because sudo clears environment variables (at least for me on OS X > Lion). You could possibly do this instead: > > sudo make USE_PGXS=1 install > > It would still be running without the custom PATH setting, but likely > you don't need that for the install step. A thousand thanks!! This seems to work. At least, it copied the files over to /usr/local/pgsql-9.1/share/extension/. Unfortunately, I can't yet check the integration of the extension into my database, as my database doesn't want to start up (see my other mail from this morning). Thanks so much! -- 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] Please make it easy to drop a database that is in use
Like I said in my original post, I understand the workaround. I just think that: 1) The workaround requires extra work for each developer (or at least each client application) using PostgreSQL, while a fix in PostgreSQL would solve this once and for all. 2) The workaround requires superuser privileges, which I don't think should be required to drop your own database. Regards, Evan On 20/06/2012 10:51 PM, Sergey Konoplev wrote: On Tue, Jun 19, 2012 at 1:40 PM, Evan Martin wrote: When I'm developing against a PostgreSQL database I often drop and re-create it and I often find that the drop fails, because it's "in use by other users". This is really annoying, especially when I know full well there are no other users - it's just me. Just connect another (say postgres) database and disconnect the database you are trying to delete. And keep it in mind. hassle, for something that should be a very simple operation. (I'm not even writing SQL for it normally, just pressing Delete in pgAdmin.) Secondly, So I think this proposal/issue should be sent not to PG development team but to pgAdmin's one. Clients software should make all this re-connections accordingly to its own rules. pg_terminate_backend requires superuser rights. If I'm not a superuser, but I am the owner of the database, it doesn't seem right that another user should be able to prevent me from dropping my database. I'd really like to see PostgreSQL directly support dropping a database, regardless of who is using it - something like "DROP DATABASE ... CASCADE". (Although "CASCADE" wouldn't be the appropriate word here. Maybe "DROP DATABASE ... TO_HELL_WITH_USERS"?) Evan -- 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] Output of query_to_xml
2012/6/21 P. Broennimann :
> Hi there
>
> In my stored function I use:
>
> select query_to_xml('select * from table12', true, true, '') into ...
>
>
> The result is OK but there is always an empty line:
>
> http://www.w3.org/2001/XMLSchema-instance";>
> <-- Empty line here
> FG8976SDFRETG
> NL
> 2011-02-28
> MFT
> GAB
> TAFP
>
>
> Is this supposed to be or is this a cosmetic bug?
In fact this is in code [1], obviously, but if it should be there I don't know.
That extra line only is printed when 'tableforest' is set to true, so
if you use:
select query_to_xml('select * from table12', true, false, '')
the extra line and the 'xmlns:xsi=' for tag goes always.
[1]
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/xml.c;h=44d327d7606df620c01b6e6532e53a9ee7a21f23;hb=HEAD#l2386
regards
--
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
--
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] 32-bit libpq with 64-bit server
-Message d'origine- De : Merlin Moncure [mailto:mmonc...@gmail.com] Envoyé : June-20-12 11:36 AM It should pretty much work. The main thing to watch out for is that very large results sets will hit the upper limit on memory allocations. merlin De : Craig Ringer [mailto:ring...@ringerc.id.au] Envoyé : June-20-12 10:47 PM On 06/20/2012 11:07 PM, Marc Watson wrote: Hello all, On Windows, I have a 32-bit client application that uses the 32-bit libpq.dll. In testing the client application with a Postgres 9.2 64-bit server I've noticed no problems, and was wondering if anyone knows of any caveats in this - AFAICS the tcp communication is between the 32-bit libpq and the 64-bit backend is compatible. Unless you go to considerable efforts to ensure otherwise, the protocol is text-based and isn't affected by endianness or word size. I'm not sure about the binary protocol, but it's rarely used. -- Craig Ringer Thanks Craig and Merlin. I'll continue with my testing of 9.2-64 bit. Mark Watson
[GENERAL] Reading storage parameters
Hello, is there a way to read the storage parameters values set on a table (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and so on...)? I can't find it in the docs. -- Daniele -- 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] Please make it easy to drop a database that is in use
On Thu, Jun 21, 2012 at 2:03 PM, Evan Martin wrote: > 1) The workaround requires extra work for each developer (or at least each > client application) using PostgreSQL, while a fix in PostgreSQL would solve > this once and for all. It is not clean what database you need to reconnect automatically after the dropping. Moreover you may not have permissions to connect other databases. > 2) The workaround requires superuser privileges, which I don't think should > be required to drop your own database. It does not require it. You might also be an owner to drop the database. > > Regards, > > Evan > > > On 20/06/2012 10:51 PM, Sergey Konoplev wrote: >> >> On Tue, Jun 19, 2012 at 1:40 PM, Evan Martin >> wrote: >>> >>> When I'm developing against a PostgreSQL database I often drop and >>> re-create >>> it and I often find that the drop fails, because it's "in use by other >>> users". This is really annoying, especially when I know full well there >>> are >>> no other users - it's just me. >> >> Just connect another (say postgres) database and disconnect the >> database you are trying to delete. And keep it in mind. >> >>> hassle, for something that should be a very simple operation. (I'm not >>> even >>> writing SQL for it normally, just pressing Delete in pgAdmin.) Secondly, >> >> So I think this proposal/issue should be sent not to PG development >> team but to pgAdmin's one. Clients software should make all this >> re-connections accordingly to its own rules. >> >>> pg_terminate_backend requires superuser rights. If I'm not a superuser, >>> but >>> I am the owner of the database, it doesn't seem right that another user >>> should be able to prevent me from dropping my database. >>> >>> I'd really like to see PostgreSQL directly support dropping a database, >>> regardless of who is using it - something like "DROP DATABASE ... >>> CASCADE". >>> (Although "CASCADE" wouldn't be the appropriate word here. Maybe "DROP >>> DATABASE ... TO_HELL_WITH_USERS"?) >>> >>> Evan >> >> >> > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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] Reading storage parameters
On 21 June 2012 13:12, Daniele Varrazzo wrote: > Hello, > > is there a way to read the storage parameters values set on a table > (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and > so on...)? I can't find it in the docs. SELECT c.reloptions FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = 'tablename' AND n.nspname = 'schemaname'; -- Thom -- 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] Reading storage parameters
On Thu, Jun 21, 2012 at 5:42 PM, Daniele Varrazzo <
daniele.varra...@gmail.com> wrote:
> Hello,
>
> is there a way to read the storage parameters values set on a table
> (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and
> so on...)? I can't find it in the docs.
>
>
One way is with
\d+
Second with pg_class.
postgres=# select relname,reloptions from pg_class where relname='foo';
relname | reloptions
-+
foo | {autovacuum_enabled=false}
(1 row)
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Reading storage parameters
On Thu, Jun 21, 2012 at 1:26 PM, Thom Brown wrote: > On 21 June 2012 13:12, Daniele Varrazzo wrote: >> Hello, >> >> is there a way to read the storage parameters values set on a table >> (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and >> so on...)? I can't find it in the docs. > > SELECT c.reloptions > FROM pg_class c > INNER JOIN pg_namespace n > ON c.relnamespace = n.oid > WHERE c.relname = 'tablename' > AND n.nspname = 'schemaname'; Ok, so they are in pg_class.reloptions, thank you! -- Daniele -- 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] Please make it easy to drop a database that is in use
On 21/06/2012 10:20 PM, Sergey Konoplev wrote: On Thu, Jun 21, 2012 at 2:03 PM, Evan Martin wrote: 1) The workaround requires extra work for each developer (or at least each client application) using PostgreSQL, while a fix in PostgreSQL would solve this once and for all. It is not clean what database you need to reconnect automatically after the dropping. Moreover you may not have permissions to connect other databases. That's a fair point, so perhaps DROP DATABASE should still fail if the /current/ connection is to that database (preferably with a helpful error like "you cannot drop the database you are connected to"). There should be an easy way to close all /other/ connections to it, though. 2) The workaround requires superuser privileges, which I don't think should be required to drop your own database. It does not require it. You might also be an owner to drop the database. It does when I try it: SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'dropme'; ERROR: must be superuser to signal other server processes In this case the user was the owner of "dropme", but another user was also connected to it. I believe that should not stop the owner from dropping their database. Regards, Evan
Re: [GENERAL] Please make it easy to drop a database that is in use
> > > SELECT pg_terminate_backend(procpid) > FROM pg_stat_activity > WHERE datname = 'dropme'; > > ERROR: must be superuser to signal other server processes > > You can try this approach. http://archives.postgresql.org/pgsql-general/2012-04/msg00100.php --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Please make it easy to drop a database that is in use
Evan Martin wrote: > Like I said in my original post, I understand the workaround. I just > think that: > > 1) The workaround requires extra work for each developer (or at least > each client application) using PostgreSQL, while a fix in PostgreSQL > would solve this once and for all. For a developer it should not be a problem to write an auxiliary function that kills the connections and drops the database. > 2) The workaround requires superuser privileges, which I don't think > should be required to drop your own database. You said that such a feature would be useful for developers. Developers usually have superuser privileges. PostgreSQL 9.3 will probably allow you to pg_terminate_backend() your own sessions even if you are not a superuser, at least there is such a patch in the queue. I don't think that saving a few keystrokes for lazy developers is a good enough reason for such a thing in core. Yours, Laurenz Albe -- 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] Please make it easy to drop a database that is in use
Evan Martin writes: > That's a fair point, so perhaps DROP DATABASE should still fail if the > /current/ connection is to that database (preferably with a helpful > error like "you cannot drop the database you are connected to"). It does that. > SELECT pg_terminate_backend(procpid) > FROM pg_stat_activity > WHERE datname = 'dropme'; > ERROR: must be superuser to signal other server processes As far as that goes, there's a pending patch to reduce the privileges required to use pg_terminate_backend. I'm not in favor of having DROP DATABASE do it for you though --- that just seems like a very large-caliber foot gun. 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] Error message "psql: could not connect to server: No such file or directory"
On 06/20/2012 10:40 PM, Stefan Schwarzer wrote: I need unfortunately to come back to this issue. I (again) re-installed Lion from scratch, and finally got everything working. The Postgres was running, I uploaded a couple of dumped SQL files. And then re-started the machine for another reason. And suddenly it says again the it can't connect to the server. I tried /usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US and /usr/local/pgsql-9.1/bin/pg_ctl -D /usr/local/pgsql-9.1/data/ -l logfile start and sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist … but nothing happens. I don't have the feeling that it's a problem with the "postgres" and "_postgres" users (as it has worked before already). Can anyone give me a hint how I can figure out where the problem lies and what I can do about it? Why did it work before I restarted the computer and why does it not work anymore? Thanks a lot for any hints! When troubleshooting I am firm believer in working from the known to the unknown. In that vein: 1) Is there /usr/local/pgsql-9.1/data/? Yes: drwx-- 18 _postgres _postgres 612 Jun 19 09:41 data a) If so are there subdirectories in it indicating cluster was created? i.e. base/ global/ pg_clog/, etc Yes: drwx-- 7 _postgres _postgres238 Jun 19 08:59 base drwx-- 43 _postgres _postgres 1462 Jun 19 09:41 global …. 2) When you say nothing happens does that mean nothing is written to log file and/or terminal? No process shows up in process list? When I do: sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist it says:org.postgresql.postgres: Already loaded When I do: /usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US it says:The files belonging to this database system will be owned by user "xxx". This user must also own the server process. The database cluster will be initialized with locale en_US. The default text search configuration will be set to "english". initdb: could not access directory "/usr/local/pgsql-9.1/data": Permission denied So at a guess user 'xxx' is not '_postgres'. You need to be the _postgres user when doing the initdb. Same when using "_postgres" instead of "postgres". When I do: sudo su - _postgres /usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US it seems to run, but still, same error message "psql: could not connect…." See, here the initdb worked. Doing the inidtdb is not the same as starting the database. initdb only creates the initial database cluster. To start the database you need to do something like(as _postgres): /usr/local/pgsql-9.1/bin/pg_ctl start -D /usr/local/pgsql-9.1/data And still nothing in the process list (using "ps auxw | grep post"). 3) If something does happen in, terms of error messages, what are they? 4) Have you looked at system (not Postgres specific) logs to see if this a system issue? I looked at Apple's Console, where all (?) logs/messages are being assembled, but nothing worrisome there. is there any other specific place where I can look for error messages? 5) What user are you running the above commands as? I am user with "Administrator" rights. The commands need to be run as the _postgres user. Postgres will not run as root. a) What user is associated with the Postgres data directory(if it exists)? See above, "_postgres". There is or could be confusion between Apple's "_postgres" and the normal "postgres" user. But it looks as it isn't in this case.. Thanks for your help! -- 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] (BUG ?) unprefixed oid -> ERROR: cache lookup failed for function
(9.1.4) Hello, following query is wrong in my understanding, as it doesn't specify which oid to use (pg_proc or pg_roles ?) but it is accepted by the parser select pg_get_functiondef(oid) -- should be pg_get_functiondef(pg_proc.oid) from pg_proc join pg_roles on ( pg_proc.proowner=pg_roles.oid ) LIMIT 1 ERROR: cache lookup failed for function 10 EXPLAIN VERBOSE: Limit (cost=0.00..0.31 rows=1 width=4) Output: (pg_get_functiondef(pg_authid.oid)) -> Nested Loop (cost=0.00..1028.66 rows=3337 width=4) Output: pg_get_functiondef(pg_authid.oid) Join Filter: (pg_proc.proowner = pg_authid.oid) -> Seq Scan on pg_catalog.pg_proc (cost=0.00..318.37 rows=3337 width=4) Output: pg_proc.proname, pg_proc.pronamespace, pg_proc.proowner, pg_proc.prolang, pg_proc.procost, pg_proc.prorows, pg_proc.provariadic, pg_proc.proisagg, pg_proc.proiswindow, pg_proc.prosecdef, pg_proc.proisstrict, pg_proc.proretset, pg_proc.provolatile, pg_proc.pronargs, pg_proc.pronargdefaults, pg_proc.prorettype, pg_proc.proargtypes, pg_proc.proallargtypes, pg_proc.proargmodes, pg_proc.proargnames, pg_proc.proargdefaults, pg_proc.prosrc, pg_proc.probin, pg_proc.proconfig, pg_proc.proacl -> Materialize (cost=0.00..1.21 rows=14 width=4) Output: pg_authid.oid -> Seq Scan on pg_catalog.pg_authid (cost=0.00..1.14 rows=14 width=4) Output: pg_authid.oid best regards, Marc Mamin
Re: [GENERAL] Please make it easy to drop a database that is in use
On 22/06/2012 12:07 AM, Tom Lane wrote: SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'dropme'; ERROR: must be superuser to signal other server processes As far as that goes, there's a pending patch to reduce the privileges required to use pg_terminate_backend. I'm not in favor of having DROP DATABASE do it for you though --- that just seems like a very large-caliber foot gun. Good to hear there's a patch coming for that. Just to be clear, I'm not suggesting DROP DATABASE should do that by default. Basically, I'm looking for the "--force" option here - a way to say "I know what I'm doing, just drop this database if at all possible". "rm -rf" is a foot gun, too, but if that -f wasn't there you would have to write commands to set permissions on files when you couldn't care less about the permissions and just want to delete them. Sure, a competent Linux user could write such a command, but imagine how annoying it would be to do that all the time! Not to mention the extra room for errors in that command. To me, DROP DATABASE is a very similar case. Regards, Evan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Promoting sync slave to master without incrementing timeline counter?
Hi all, Given a cluster of three database servers running 9.1.3 (master, sync slave, async slave), it seems that there are two ways to promote the sync slave to become master: 1. pg_ctl promote the sync slave (increments timeline counter) 2. remove recovery.conf on the sync slave and pg_ctl restart (does not increment timeline counter) The sync slave becomes master more quickly using `pg_ctl promote`, but now every server in the cluster has to take a new base backup due to the incremented timeline. 2ndQuadrant's repmgr uses the second option so that the async slave can "follow" the new master, saving you from having to do a new base backup. Additionally, the old master is able to start streaming replication from the new master without a new base backup. (Repmgr does not actually support the latter behavior out of the box, but it seemed to work.) So, given a hard failure (i.e. power loss) of the master, `pg_ctl promote` provides availability more quickly, but `pg_ctl restart` provides data redundancy more quickly. Is this an accurate assessment of the tradeoffs between the two approaches? I've found talk on the mailings lists surrounding future support for slaves following timelines after a new master completes recovery, but I have been unable to find anything discussing the approach used by repmgr. Are there risks associated with the `pg_ctl restart` approach, or is it safe to use? Cheers, Dave
[GENERAL] Conditional cast for eg sorting?
I'm writing a tool for web-based management of DNS records, and I've come up against a UI nuisance that I'm hoping I can get solved in Postgres instead of some higher layer. One table contains all of the live records: CREATE TABLE records ( domain_id integer NOT NULL DEFAULT 0, rdns_id integer NOT NULL DEFAULT 0, record_id serial NOT NULL, host text DEFAULT '' NOT NULL, "type" integer DEFAULT 1 NOT NULL, val text DEFAULT '' NOT NULL, distance integer DEFAULT 0 NOT NULL, weight integer DEFAULT 0 NOT NULL, port integer DEFAULT 0 NOT NULL, ttl integer DEFAULT 7200 NOT NULL, description text ); host is the hostname, val is the target or result for forward zones For reverse zones, val is the IP (strictly speaking, the ip6.arpa or in-addr.arpa "hostname", stored as an IP address and converted on export), and host is the resulting hostname. For reverse zones I can simply sort on CAST(val AS inet), since val should never be anything other than a valid IP or CIDR. For forward zones, though, I can't just unconditionally cast the column as inet, because there are all kinds of values that are not valid IP or CIDR addresses. I still want to sort the IPs in this field properly though; eg, 192.168.1.100 should come just after 192.168.1.99, not 192.168.1.10. Is there any way to conditionally cast a value for sorting? I don't care if IP addresses end up in a big block at the beginning or end of the list so long as it's consistent. -kgd -- 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] (BUG ?) unprefixed oid -> ERROR: cache lookup failed for function
"Marc Mamin" writes: > following query is wrong in my understanding, as it doesn't specify > which oid to use (pg_proc or pg_roles ?) > but it is accepted by the parser > select pg_get_functiondef(oid) -- should be > pg_get_functiondef(pg_proc.oid) > from pg_proc join pg_roles > on ( pg_proc.proowner=pg_roles.oid ) > LIMIT 1 The reason that happens is that pg_roles has an explicit oid column (ie, oid is a regular not system column in the view) so that name takes precedence over the system column available from pg_proc. Having to have an explicit oid column in pg_roles isn't very nice, because of precisely this type of inconsistency, but since it's a view not a table there's not a lot of alternatives. 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] Conditional cast for eg sorting?
On Jun 21, 2012, at 8:45 AM, Kris Deugau wrote: > I'm writing a tool for web-based management of DNS records, and I've > come up against a UI nuisance that I'm hoping I can get solved in > Postgres instead of some higher layer. > > One table contains all of the live records: > > CREATE TABLE records ( >domain_id integer NOT NULL DEFAULT 0, >rdns_id integer NOT NULL DEFAULT 0, >record_id serial NOT NULL, >host text DEFAULT '' NOT NULL, >"type" integer DEFAULT 1 NOT NULL, >val text DEFAULT '' NOT NULL, >distance integer DEFAULT 0 NOT NULL, >weight integer DEFAULT 0 NOT NULL, >port integer DEFAULT 0 NOT NULL, >ttl integer DEFAULT 7200 NOT NULL, >description text > ); > > host is the hostname, val is the target or result for forward zones > > For reverse zones, val is the IP (strictly speaking, the ip6.arpa or > in-addr.arpa "hostname", stored as an IP address and converted on > export), and host is the resulting hostname. > > For reverse zones I can simply sort on CAST(val AS inet), since val > should never be anything other than a valid IP or CIDR. It's valid to have other entries in in-addr.arpa zones. TXT, NS and CNAME are fairly common - see RFC 2317 or 4183, or the DeGroot hack. If you block those in your UI, you're putting artificial limits on what your users can do (and there's been a long history of regretting that, back at least to verisign's web interface being unable to add SPF records). If you don't block them in your UI you risk your queries throwing errors and failing due to that cast. > For forward zones, though, I can't just unconditionally cast the column > as inet, because there are all kinds of values that are not valid IP or > CIDR addresses. I still want to sort the IPs in this field properly > though; eg, 192.168.1.100 should come just after 192.168.1.99, not > 192.168.1.10. > > Is there any way to conditionally cast a value for sorting? I don't > care if IP addresses end up in a big block at the beginning or end of > the list so long as it's consistent. I suspect that users aren't going to care about sorting by the RHS much, rather they're going to want to sort by the LHS, so it's probably not an issue that'll be too serious with real DNS data. In general, though, I'd create a function that took "type" and "val" and converted them into a string suitable for sorting on. At it's simplest that might leave everything but A records as-is, and convert A records to something sortable: lpad(split_part($1, '.', 1), 3, '000') || lpad(split_part($1, '.', 2), 3, '000') || lpad(split_part($1, '.', 3), 3, '000') || lpad(split_part($1, '.', 4), 3, '000'); (Wrapping that in a SQL or pl/pgsql function with CASE statement to handle A records differently is left as an exercise for the reader :) ). Then you can order by the result of that function, and it should seem sensible to the user. Cheers, Steve -- 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] Conditional cast for eg sorting?
Steve Atkins wrote: > > On Jun 21, 2012, at 8:45 AM, Kris Deugau wrote: >> CREATE TABLE records ( >>domain_id integer NOT NULL DEFAULT 0, >>rdns_id integer NOT NULL DEFAULT 0, >>record_id serial NOT NULL, >>host text DEFAULT '' NOT NULL, >>"type" integer DEFAULT 1 NOT NULL, >>val text DEFAULT '' NOT NULL, >>distance integer DEFAULT 0 NOT NULL, >>weight integer DEFAULT 0 NOT NULL, >>port integer DEFAULT 0 NOT NULL, >>ttl integer DEFAULT 7200 NOT NULL, >>description text >> ); > It's valid to have other entries in in-addr.arpa zones. TXT, NS and > CNAME are fairly common - see RFC 2317 or 4183, or the DeGroot > hack. *nod* Actually, it works out fine: (Note, type is really stored as the suitable value; just using the common abbreviations for convenience. A+PTR is a stored pseudotype that exports to tinydns' "=" record, publishing both an A record and PTR record. If/when I ever get around to implementing BIND export, it would create the separate A and PTR records as appropriate.) forward zone example.com: host type val example.com NS ns1.example.com foo.example.com A 192.168.2.4 both.example.com A+PTR 192.168.2.5 reverse zone 192.168.2.0/24: host type val ns2.example.com NS 192.168.2.0/24 notfoo.example.com PTR192.168.2.6 both.example.com A+PTR 192.168.2.5 ns.small.company NS 192.168.2.16/28 16.16-31.2.168.192.in-addr.arpa CNAME 192.168.2.16 17.16-31.2.168.192.in-addr.arpa CNAME 192.168.2.17 ... 31.16-31.2.168.192.in-addr.arpa CNAME 192.168.2.31 (Or just use the handy "Delegate" pseudotype I've implemented, which creates both the NS record and all necessary CNAME records on export instead of making you manage them by hand.) reverse zone 192.168.2.16/28: hosttype val ns.small.companyNS192.168.2.16/28 mail.small.company PTR 192.168.2.18 Notice that the "LHS/RHS" logic of the host/val columns is inverted for reverse zones; this was required for the A+PTR type which uses only one record, but with nonzero values in both the domain_id and rdns_id FK columns to refer to both zones. The IPs and CIDR blocks are converted to a suitable in-addr.arpa name on export. Someday I may add a configuration flag, or even a per-use-case flag, to pick which delegation scheme to use for sub-/24 blocks, but this one seemed to make the most sense to me. I hadn't thought about allowing TXT records in reverse zones; but I don't see any real problems with allowing it. > I suspect that users aren't going to care about sorting by the RHS > much, rather they're going to want to sort by the LHS, > so it's probably not an issue that'll be too serious with real DNS > data. *nod* It's a pretty minor irritation, on the whole. And in large zones the UI's filtering/searching capability would be more useful in finding a specific record. > In general, though, I'd create a function that took "type" and "val" and > converted them into a string suitable for sorting on. At it's simplest > that might leave everything but A records as-is, and convert > A records to something sortable: > > lpad(split_part($1, '.', 1), 3, '000') || lpad(split_part($1, '.', 2), 3, > '000') || lpad(split_part($1, '.', 3), 3, '000') || lpad(split_part($1, '.', > 4), 3, '000'); Thanks for the pointer. I'll have to play with it to see if it runs fast enough to not be a nasty performance drain. > (Wrapping that in a SQL or pl/pgsql function with CASE statement to > handle A records differently is left as an exercise for the reader :) ). > > Then you can order by the result of that function, and it should seem > sensible to the user. :P That helps too, I wasn't sure what to look for in the docs to find out where to wedge this in. -kgd -- 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] Promoting sync slave to master without incrementing timeline counter?
On Thu, Jun 21, 2012 at 10:10 AM, David Pirotte wrote: > > 2ndQuadrant's repmgr uses the second option so that the async slave can > "follow" the new master, saving you from having to do a new base backup. > Additionally, the old master is able to start streaming replication from the > new master without a new base backup. (Repmgr does not actually support the > latter behavior out of the box, but it seemed to work.) > is not safe to make old master to start SR from new master without any additional action. if the old master crashed/disconnected before some info was sent to the slave, then the old master has info not in the slave so when it converts in new master that piece of info is lost... if now the old master tries to connect to the new master he will except that info to exists... > So, given a hard failure (i.e. power loss) of the master, `pg_ctl promote` > provides availability more quickly, but `pg_ctl restart` provides data > redundancy more quickly. Is this an accurate assessment of the tradeoffs > between the two approaches? yes, i think that's pretty much the difference > Are there risks associated with the `pg_ctl > restart` approach, or is it safe to use? > it's safe as long as you let repmgr do it ;) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] Promoting sync slave to master without incrementing timeline counter?
On 21 June 2012 16:10, David Pirotte wrote: > So, given a hard failure (i.e. power loss) of the master, `pg_ctl promote` > provides availability more quickly, but `pg_ctl restart` provides data > redundancy more quickly. Not sure where this idea of "more quickly" comes from. Can you explain? > Are there risks associated with the `pg_ctl > restart` approach, or is it safe to use? PostgreSQL supports both, why do you mention just one of them as a potential risk? -- Simon Riggs 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] Error message "psql: could not connect to server: No such file or directory"
>> >> When I do: sudo launchctl load >> /Library/LaunchDaemons/org.postgresql.postgres.plist >> it says: org.postgresql.postgres: Already loaded >> >> When I do: /usr/local/pgsql-9.1/bin/initdb -U postgres -D >> /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US >> it says:The files belonging to this database system will be >> owned by user "xxx". >>This user must also own the server process. >>The database cluster will be initialized with locale >> en_US. >>The default text search configuration will be set to >> "english". >>initdb: could not access directory >> "/usr/local/pgsql-9.1/data": Permission denied > > > So at a guess user 'xxx' is not '_postgres'. You need to be the _postgres > user when doing the initdb. > >> >> Same when using "_postgres" instead of "postgres". >> >> When I do: sudo su - _postgres /usr/local/pgsql-9.1/bin/initdb -U >> postgres -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US >> it seems to run, but still, same error message "psql: could not connect…." > > See, here the initdb worked. Doing the inidtdb is not the same as starting > the database. initdb only creates the initial database cluster. > > To start the database you need to do something like(as _postgres): > > /usr/local/pgsql-9.1/bin/pg_ctl start -D /usr/local/pgsql-9.1/data Thanks so much for those tips. If I understand it correctly, the commands should be then: sudo su - _postgres /usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US sudo su - _postgres /usr/local/pgsql-9.1/bin/pg_ctl start -D /usr/local/pgsql-9.1/data Although I don't get an error message, I don't have the feeling that it started the server. There is still nothing in the process list, and a normal "psql" results in the same message as before "psql: could not connect…" So sorry for bothering you guys… just being really frustrated now… If you have any ideas, please let me know… :-) Stef -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
