Re: [GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.
leaf_yxj wrote: > My bosses ask me to list > > 1)all the users and the roles associated with the users. This will list all roles in the cluster, whether they can login (are users) or not, and the array of all roles they are directly or indirectly members of: WITH RECURSIVE is_member_of(member, roleid) AS (SELECT oid, oid FROM pg_roles UNION SELECT m.member, r.roleid FROM is_member_of m JOIN pg_auth_members r ON (m.roleid = r.member)) SELECT u.rolname, u.rolcanlogin, array_agg(r.rolname) AS belongs_to FROM is_member_of m JOIN pg_roles u ON (m.member = u.oid) JOIN pg_roles r ON (m.roleid = r.oid) GROUP BY u.rolname, u.rolcanlogin; > 2) all the users and the privileges associated with that users. That is pretty difficult. You have to construct queries for each type of object. The following query will show the desired information for all privileges on tables, views and sequences (but not privileges granted on columns of tables): SELECT rolname, tablename, privilege_type, is_grantable FROM ( SELECT r.rolname, r.oid, n.nspname || '.' || t.relname AS tablename, (aclexplode(t.relacl)).grantee, (aclexplode(t.relacl)).privilege_type, (aclexplode(t.relacl)).is_grantable FROM pg_shdepend shd JOIN pg_roles r ON (shd.refobjid = r.oid) JOIN pg_class t ON (shd.objid = t.oid) JOIN pg_namespace n ON (t.relnamespace = n.oid) WHERE shd.classid = 'pg_class'::regclass AND shd.deptype='a' AND objsubid = 0) AS q WHERE oid = grantee; You'd have to construct similar queries for all other objects for which privileges can be granted: table columns, databases, foreign data wrappers, foreign servers, functions, languages, large objects, schemata. I leave this as exercise for the reader. 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] Versioned, chunked documents
On 02/04/2012 01:52, Martin Gregorie wrote: > BTW, why use document_chunks when a text field can hold megabytes, > especially if they will be concatenated to form a complete document > which is then edited as a whole item and before being split into chunks > and saved back to the database? ... because neither of your assumptions are true :) They will not hold megabytes, they will not often be concatenated, the document is never edited in whole, and I do *not* need version numbers on the whole document :) signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Re: [GENERAL] pg_dump incredibly slow dumping a single schema from a large db
:) yah that makes sense no big deal. i'll probably just push this head buiild of pg_dump onto the production machines till it comes out. Thanks again! On Sat, Mar 31, 2012 at 3:44 PM, Tom Lane wrote: > Mike Roest writes: > > Any idea when 9.1.4 with this change will be out so we can pull the > cluster > > up. > > Well, we just did some releases last month, so unless somebody finds a > really nasty security or data-loss issue, I'd think it will be a couple > of months before the next set. > >regards, tom lane >
Re: [GENERAL] Trigger.. AFTER and BEFORE with specific column changed
On 04/01/2012 10:10 PM, Albert wrote: *i get:* Syntax error at or near 'WHEN' LINE 1: ... check_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) Hmmm. So two questions: What happens if you copy the example in the docs exactly and use NEW.* and OLD.*? What is the table definition for accounts? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trigger-AFTER-and-BEFORE-with-specific-column-changed-tp5610712p5611501.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Mac OS X Lion how to connect to remote server ?
I've installed PostgreSQL using one click installer on Mac OS X Lion. I'd like to query this server from the net. How to allow specific IPV6 addresses to connect to the databases 'addressbook', 'cli', 'landp' and 'landp_public' ? I've tested a simple solution using ssh port forwarding : $ ssh -L :localhost:5432 yt@iMac But I'd like not to use this solution, I'd better use tcp ip. Then how to setup pg_hba.conf and postgresql.conf for that purpose ? Does i need to open TCPIP port 5432, and how ? -- Yvon
Re: [GENERAL] Trigger.. AFTER and BEFORE with specific column changed
Albert writes: > *i get:* > Syntax error at or near 'WHEN' > LINE 1: ... check_update AFTER UPDATE ON accounts FOR EACH ROW WHEN > (OLD.balance IS DISTINCT FROM NEW.balance) Does the BEFORE case work for you either? I'm suspicious that you are using a PG version that predates WHEN-clause support in triggers. 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
[GENERAL] User-defined Aggregate function and performance.
Hello. I've tried asking this on the irc channel, without much success. I'm trying to define a "weighted mean" aggregate using postgresql create aggregate feature. I've been able to quickly write the required pgsql code to get it working, but after testing it on a sample 1 rows table, it seems to be approximately 6 to 10 times slower than pure sql. My initial implementation was in pl/pgsql, and did not mark the functions as immutable. I did so after a suggestion from an irc user, but it did not change anything performance wise. Any idea on how to make it faster ? Here is the code: create type _weighted_avg_type as ( running_sum numeric, running_count numeric ); create or replace function mul_sum (a _weighted_avg_type, amount numeric, weight numeric) returns _weighted_avg_type as $$ select ((($1.running_sum + ($2 * $3)) , ($1.running_count + $3) ))::_weighted_avg_type; $$ language sql immutable; create or replace function final_sum (a _weighted_avg_type) returns numeric as $$ SELECT CASE WHEN $1.running_count = 0 THEN 0 ELSE $1.running_sum / $1.running_count END; $$ language sql immutable; create aggregate weighted_avg (numeric, numeric)( sfunc = mul_sum, finalfunc = final_sum, stype = _weighted_avg_type, initcond = '(0,0)' ); create temp table test as (select a::numeric, b::numeric from generate_series(1, 100) as t1(a), generate_series(1, 100) as t2(b)); -- Custom aggregate select weighted_avg(a, b) from test; -- pure sql version select case when sum(b::numeric) = 0 then 0 else sum(a::numeric * b::numeric) / sum(b::numeric) end from test; -- Ronan Dunklau -- 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.exe on windows format on command line that starts the process
In Windows PostgreSQL spawns numerous postgres.exe processes. When you look at the actual command line you see the command that started the process. For example: "c:/PostgreSQL/8.4/bin/postgres.exe" "--forkbackend" "1372" My questions 1. In the example above does 1372 represent a port? 2. If it is a port then how can I change the ports used OR avoid using certain ports? Thanks, Bruce.
Re: [GENERAL] User-defined Aggregate function and performance.
Ronan Dunklau writes: > I'm trying to define a "weighted mean" aggregate using postgresql create > aggregate feature. > I've been able to quickly write the required pgsql code to get it > working, but after testing it on a sample 1 rows table, it seems to > be approximately 6 to 10 times slower than pure sql. It might help to use a two-element array for the transition state, instead of a custom composite type. > My initial implementation was in pl/pgsql, and did not mark the > functions as immutable. I did so after a suggestion from an irc user, > but it did not change anything performance wise. Those suggestions would possibly help for a function that's meant to be inlined into larger SQL expressions, but they won't do much for an aggregate support function. I'm not real sure, but I think plpgsql might be faster in this context. Another thing to think about is whether you really need type numeric here. float8 would be a lot faster ... though you might have roundoff issues. 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
[GENERAL] Fwd: [HACKERS] Switching to Homebrew as recommended Mac install?
Moving from hackers Begin forwarded message: > From: Jay Levitt > Date: April 2, 2012 5:17:07 MST > To: Dave Page > Cc: PG Hackers > Subject: Re: [HACKERS] Switching to Homebrew as recommended Mac install? > > > This goes back to the "marketing challenge, not technical challenge" threads. > Actually it seems the question is whether we suggest trading security for convenience. If the rest of PostgreSQL is something these whiz kids want or need to use they will learn what is needed to install it. If they work for a company likely someone will train them. If they do not they will teach the selves from some form of textbook - whether that be a blog post or official high-quality documentation. > > Sure, and if humans read docs, instead of just glancing at them, that'd be > all you needed. In any case, I could counter myself that nobody reads the doc > period, so it doesn't matter what version is listed; that's just the source > of my own misunderstanding about maintenance. In a project of this magnitude there is only so much you can learn via trial and error. Thus original learning requires some (multiple) forms of written text. Once you have some people with the knowledge you can train others. The documentation is there and really smart people will realize that they cannot know everything and so they should read documentation. Its our job to make sure the trough is full. If the horse dehydrates because it doesn't want to drink I don't lose any sleep - I'll just look for a different horse. That said I'll at least to make the software indicate when there is an unusual configuration and give the user some guidance on what to look for. But I'd generally rather strictly notify and let the user make an informed, manual, decision on how to resolve the issue instead of assuming and hoping the cure is no worse than the disease. > > - There are eight ways to install Postgres on a Mac That isn't any more of a reason to discount the EDB installer than any other. >>> Nope, just an argument that the recommended installer should handle that >>> nicely. >> >> It does. It'll detect that the port is in use and suggest a different >> one. I don't know of any other of those installation methods that'll >> do that. > > That's terrific, and you're right - it's the only one. > > > > In that vein, here's my take on the average whiz kid of the next five years: > > Most of those seem irrelevant and none of them are followed by specific actions the community can take to adapt to the new normal. > You get the idea. Just as we grew up not having to solder our computers > (much) or boot them from the front panel, they've always had a whole layer of > infrastructure that either magically works or that they can fix by Googling > and typing without understanding the internals. (GitHub did not need their > own sysadmins until December.) Let's make PG part of that. That means nothing without more context. And while making Postgres "part of that" sounds all good I still haven't and good reasons the status quo is failing nor specific actions that can be taken toward that end. The few things you mentioned you can do without any permission from the community and getting listed on the website seems to require that you make security a higher priority. That's just how we roll here and it is highly unlikely that will change. > > > Jay > > David J.
[GENERAL] Fwd: [HACKERS] Switching to Homebrew as recommended Mac install? / apology
Moving from Hackers as well... Begin forwarded message: > From: Jay Levitt > Date: April 2, 2012 9:02:49 MST > To: David Johnston > Cc: Tom Lane , PG Hackers > Subject: Re: [HACKERS] Switching to Homebrew as recommended Mac install? / > apology > > > > Nothing at this point. I was thinking out loud, and at the time was > temporarily insa^h^h^hconvinced that the homebrew formula should be the > community standard, and thus that I'd have to bring it up to some level of > acceptability/review. I've contributed to the formula in the past, and will > continue to do so based on the thoughts everyone's shared here. It doesn't > need to be official to be useful, and as David Page said, it's not gonna be > listed in the docs no matter what, given the one decision that homebrew makes > (/usr/local) that I can't override. I'm curious if David Page et al. would list the recipie if it contained a disclaimer regarding the security flaws inherent in the underlying location choice and suggesting it only be used for developers and not production? One reason to do so would be so that people who know and would want to use homebrew would be able to see that, yes, one exists but that there are security implications resulting from its use. The recipie itself should also provide these warnings and explanations but the community download page could be used as an opportunity to try and teach these people more about security. It makes no sense to pretend that dangerous things do not exist but rather we should teach about the dangers and try to provide alternatives. David J.
Re: [GENERAL] Postgres.exe on windows format on command line that starts the process
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of br...@concena.com Sent: Monday, April 02, 2012 8:39 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Postgres.exe on windows format on command line that starts the process In Windows PostgreSQL spawns numerous postgres.exe processes. When you look at the actual command line you see the command that started the process. For example: "c:/PostgreSQL/8.4/bin/postgres.exe" "--forkbackend" "1372" My questions 1. In the example above does 1372 represent a port?>>> NO 2. If it is a port then how can I change the ports used OR avoid using certain ports? >>>NOT A PORT Thanks, Bruce. >>> The "1372" is a system process id (pid), basically a PK for a Window's process. >>> David J.
[GENERAL] Sequential Scans
Hello. What is the difference between Sequential Scans and Sequential Tuples Read?. Thank you.
Re: [GENERAL] Sequential Scans
On Mon, Apr 2, 2012 at 12:30 PM, Efraín Déctor wrote: > Hello. What is the difference between Sequential Scans and Sequential > Tuples Read?. A sequential scan is what happens when the table is read sequentially. One sequential scan can result in MANY tuples being read sequentially. I.e. a table with 1M rows being sequentially scanned from end to end will result in 1M sequential tuples being read. -- 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] Sequential Scans
Thank you. -Mensaje original- From: Scott Marlowe Sent: Monday, April 02, 2012 1:33 PM To: Efraín Déctor Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Sequential Scans On Mon, Apr 2, 2012 at 12:30 PM, Efraín Déctor wrote: Hello. What is the difference between Sequential Scans and Sequential Tuples Read?. A sequential scan is what happens when the table is read sequentially. One sequential scan can result in MANY tuples being read sequentially. I.e. a table with 1M rows being sequentially scanned from end to end will result in 1M sequential tuples being read. -- 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 check the role has been granted to which role. Help me to double check . Thanks.
Hi, what about this: SELECT p.rolname, m.rolname as member, g.rolname as grantor FROM pg_authid p INNER JOIN pg_auth_members am ON (p.oid = am.roleid) INNER JOIN pg_authid m ON (am.member = m.oid) INNER JOIN pg_authid g ON (am.grantor = g.oid) You can use proper WHERE to filter results. Regards, Bartek 2012/3/31 leaf_yxj > I want to check the role has been granted to which role. In my working > environment, the all the normal is assigned to role group. when i issue dp, > it only give me the role group privilege. So I need to check which user is > in which user group. THe following is my sql to do that. Is there anybody > has a better way to do it. Thanks. Grace > > select DISTINCT user, group_name, grantor, admin_option > from > (select usename AS user,roleid,admin_option from pg_user join > pg_auth_members on ( pg_user.usesysid=pg_auth_members.member)) a, > (select usename AS group_name,roleid from pg_user join pg_auth_members on > (pg_user.usesysid=pg_auth_members.roleid)) b, > (select usename AS grantor,roleid from pg_user join pg_auth_members on > (pg_user.usesysid=pg_auth_members.grantor)) c > > where a.roleid=b.roleid > and b.roleid=c.roleid; > > member | group_name | grantor | admin_option > ++-+-- > user1 | grace | gpadmin | f > user_1 | grace | gpadmin | f > (2 rows) > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/How-to-check-the-role-has-been-granted-to-which-role-Help-me-to-double-check-Thanks-tp5608906p5608906.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] Please help me to take a look of the erros in my functions. Thanks.
I tried to create function to truncate table 1) when the user call the function just specify the tablename 2) the user can use the function owner privilege to execute the function. But I got the errors as follows. Please help me to take a look. Thanks. Regards. Grace -- function : CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS $$ DECLARE stmt RECORD; statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables; BEGIN IF stmt IN statements then EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;'; ELSE The tablename doesn't exist.doesn END IF ; END; $$ LANGUAGE 'plpgsql' security definer; errors. ERROR: syntax error at or near "$2" LINE 1: SELECT $1 IN $2 ^ QUERY: SELECT $1 IN $2 CONTEXT: SQL statement in PL/PgSQL function "truncate_t" near line 6 -- View this message in context: http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5613507.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
Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.
Hello " IF stmt IN statements then " is nonsense. use trapping exceptions instead BEGIN EXECUTE 'TRUNCATE TABLE ' || quote_ident(_tablename) || ' CASCADE'; EXCEPTION WHEN undefined_table THEN RAISE EXCEPTION 'your own exception, when you like'; END; Regards Pavel 2012/4/2 leaf_yxj : > I tried to create function to truncate table > 1) when the user call the function just specify the tablename > 2) the user can use the function owner privilege to execute the function. > > But I got the errors as follows. Please help me to take a look. > > Thanks. > > Regards. > > Grace > -- function : > > CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS > $$ > DECLARE > stmt RECORD; > statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables; > BEGIN > IF stmt IN statements then > EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' > CASCADE;'; > ELSE > The tablename doesn't exist.doesn > END IF ; > END; > $$ LANGUAGE 'plpgsql' security definer; > > errors. > ERROR: syntax error at or near "$2" > LINE 1: SELECT $1 IN $2 > ^ > QUERY: SELECT $1 IN $2 > CONTEXT: SQL statement in PL/PgSQL function "truncate_t" near line 6 > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5613507.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 -- 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 help me to take a look of the erros in my functions. Thanks.
That is right, there is no sense to use cursors here... CREATE OR REPLACE FUNCTION truncate_t (IN tablename text) RETURNS VOID AS $$ BEGIN EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;'; EXCEPTION WHEN undefined_table THEN RAISE EXCEPTION 'Table "%" does not exists', tablename; END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT; this works fine for me. Regards, Bartek 2012/4/2 Pavel Stehule > Hello > > " IF stmt IN statements then " is nonsense. > > use trapping exceptions instead > > BEGIN > EXECUTE 'TRUNCATE TABLE ' || quote_ident(_tablename) || ' CASCADE'; > EXCEPTION WHEN undefined_table THEN > RAISE EXCEPTION 'your own exception, when you like'; > END; > > Regards > > Pavel > > > 2012/4/2 leaf_yxj : > > I tried to create function to truncate table > > 1) when the user call the function just specify the tablename > > 2) the user can use the function owner privilege to execute the function. > > > > But I got the errors as follows. Please help me to take a look. > > > > Thanks. > > > > Regards. > > > > Grace > > -- function : > > > > CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void > AS > > $$ > > DECLARE > > stmt RECORD; > > statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables; > > BEGIN > > IF stmt IN statements then > > EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' > > CASCADE;'; > > ELSE > > The tablename doesn't exist.doesn > > END IF ; > > END; > > $$ LANGUAGE 'plpgsql' security definer; > > > > errors. > > ERROR: syntax error at or near "$2" > > LINE 1: SELECT $1 IN $2 > > ^ > > QUERY: SELECT $1 IN $2 > > CONTEXT: SQL statement in PL/PgSQL function "truncate_t" near line 6 > > > > -- > > View this message in context: > http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5613507.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 > > -- > 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.1.3: launching streaming replication
i have a server in the ec2 cloud which in theory is set up as a master; it starts and runs. i've got an amazon s3 bucket mounted using s3fs on both the master and the standby (the standby is also set up in the ec2 cloud.) i followed the steps here: http://wiki.postgresql.org/wiki/Streaming_Replication and have the backup and the wal archive on the s3 bucket and they are both there. when i go to start the hot standby, i pretty consistently get LOG: entering standby mode cp: cannot stat `/db-backup/wal_archive/00010001': No such file or directory WARNING: WAL was generated with wal_level=minimal, data may be missing HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup. FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here. LOG: startup process (PID 29938) exited with exit code 1 LOG: aborting startup due to startup process failure and the startup of the hot standby fails. the Riggs admin cookbook says "you will need a short delay", but has no guesstimate on the length required. i don't even know if i'm seeing this problem or something else. if i need to run a new backup, what cleanup do i need to do of old backups and wal_archives? could this be interfering with the startup of the standby? i've gone through several iterations and fixed some problems, and wonder if there's obsolete data that is messing things up? thanks, richard
[GENERAL] Where to create an Index
I have this query:
SELECT ST_Distance(transform(geometryfromtext('POINT(-97.096667
18.858611)',4326),32614),C.point_geomutm)
AS DIST ,nombre FROM ciudad AS C ORDER BY DIST ASC limit 1;
It runs a secuential query on the table “ciudad” wich is a big table. I want to
create an index but I don’t know where, using a gist index on point_geomutm
does nothing and also creating one using nombre.
Thanks in advance.
Re: [GENERAL] Where to create an Index
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Efraín Déctor
Sent: Monday, April 02, 2012 5:40 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Where to create an Index
I have this query:
SELECT ST_Distance(transform(geometryfromtext('POINT(-97.096667
18.858611)',4326),32614),C.point_geomutm)
AS DIST ,nombre FROM ciudad AS C ORDER BY DIST ASC limit 1;
It runs a secuential query on the table “ciudad” wich is a big table. I want to
create an index but I don’t know where, using a gist index on point_geomutm
does nothing and also creating one using nombre.
Thanks in advance.
If you are using version 9.1 the following feature seems relevant:
“Add nearest-neighbor (order-by-operator) searching to GiST indexes
(Teodor Sigaev, Tom
Lane)
This allows GiST indexes to quickly return the N closest values in a query with
LIMIT. For example
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
finds the ten places closest to a given target point.”
You would index “location” in the example or “point_geomutm” in your situation.
The fact you felt an index on “nombre” might help indicates you need to read up
more about how (and when) indexes work. Since you have no explicit (or
implicit) filter on “nombre” there is no possibility that such an index would
be used. The “ORDER BY” is an implicit filter on whatever columns are being
ordered (in this case the result of the ST_Distance function). Since you
wouldn’t generally index on a function call with user-supplied parameters the
basic query cannot use an index. The 9.1 feature noted above, however, does
some kind of magic to get somewhere between brute-force and pure-index
performance.
David J.
Re: [GENERAL] Where to create an Index
Hello. Thanks for your answer.
I have the following Index:
CREATE INDEX index_pointgeomutm_ciudad
ON ciudad
USING btree_gist
(point_geomutm);
But the query is not using it.
From: David Johnston
Sent: Monday, April 02, 2012 5:04 PM
To: 'Efraín Déctor' ; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Where to create an Index
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Efraín Déctor
Sent: Monday, April 02, 2012 5:40 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Where to create an Index
I have this query:
SELECT ST_Distance(transform(geometryfromtext('POINT(-97.096667
18.858611)',4326),32614),C.point_geomutm)
AS DIST ,nombre FROM ciudad AS C ORDER BY DIST ASC limit 1;
It runs a secuential query on the table “ciudad” wich is a big table. I want to
create an index but I don’t know where, using a gist index on point_geomutm
does nothing and also creating one using nombre.
Thanks in advance.
If you are using version 9.1 the following feature seems relevant:
“Add nearest-neighbor (order-by-operator) searching to GiST indexes (Teodor
Sigaev, Tom Lane)
This allows GiST indexes to quickly return the N closest values in a query with
LIMIT. For example
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
finds the ten places closest to a given target point.”
You would index “location” in the example or “point_geomutm” in your situation.
The fact you felt an index on “nombre” might help indicates you need to read up
more about how (and when) indexes work. Since you have no explicit (or
implicit) filter on “nombre” there is no possibility that such an index would
be used. The “ORDER BY” is an implicit filter on whatever columns are being
ordered (in this case the result of the ST_Distance function). Since you
wouldn’t generally index on a function call with user-supplied parameters the
basic query cannot use an index. The 9.1 feature noted above, however, does
some kind of magic to get somewhere between brute-force and pure-index
performance.
David J.
Re: [GENERAL] 9.1.3: launching streaming replication
On Mon, Apr 2, 2012 at 4:21 PM, Welty, Richard wrote:
I got similar messages the first few times I tried to start up my slave
server, I never did figure out exactly what caused it.
You can either delete all the files on the slave and try again, or do what
I did, write a script that handles transferring just the files needed to
resync the slave.
Here's the script I've been using to transfer the files between my two
servers to resync them. This is not a production-ready script.
I have a second tablespace, so there are two 'data' transfers plus the xlog
transfer. (You may run into issues transferring the pg_tblspc directory,
as I did, hence the '-safe-links' parameter.) The '-delete' term deletes
any files on the slave that aren't on the server, unless you list them in
an '--exclude' clause.)
/usr/local/pgsql/bin/psql -c "select pg_start_backup('tardir',true)"
postgres postgres
rsync -av --exclude log.out --exclude postgresql.conf \
--exclude postgresql.pid --delete --exclude pg_hba.conf \
--exclude pg_xlog --exclude server.crt --exclude server.key \
--exclude restore.conf --exclude restore.done \
--safe-links /usr/local/pgsql/data/ postgres@xxx:/usr/local/pgsql/data
rsync -av /usr/local/pgsql/data2/ postgres@xxx:/usr/local/pgsql/data2
/usr/local/pgsql/bin/psql -c "select pg_stop_backup()" postgres postgres
rsync -av /usr/local/pgsql/data/pg_xlog postgres@xxx:/usr/local/pgsql/data/
echo "ok to start standby"
--
Mike Nolan
Re: [GENERAL] 9.1.3: launching streaming replication
On Mon, Apr 2, 2012 at 6:19 PM, Michael Nolan wrote: > > > > I got similar messages the first few times I tried to start up my slave > server, I never did figure out exactly what caused it. > > One possibility is that I may not have restarted the master server after changing the postgresql.conf file, thus the server still didn't know it was going into hot-standby mode. -- Mike Nolan
Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.
On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote: > That is right, there is no sense to use cursors here... I think you're wrong there: The OP is querying a system table for tables of a certain name, which I expect can contain multiple rows for tables of the same name in different schema's. Of course, that may not be applicable to the her situation. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- 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 help me to take a look of the erros in my functions. Thanks.
On 2 Apr 2012, at 22:02, leaf_yxj wrote: > CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS > $$ > DECLARE > stmt RECORD; > statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables; > BEGIN > IF stmt IN statements then > EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' I think you meant to use a FOR LOOP there, not IF. IF does not know to fetch a record from a CURSOR (hmm... should it perhaps?). http://www.postgresql.org/docs/9.1/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP > CASCADE;'; > ELSE > The tablename doesn't exist.doesn > END IF ; > END; > $$ LANGUAGE 'plpgsql' security definer; Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- 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] Why checkpoint_timeout had maximum value of 1h?
On 03/29/2012 06:57 AM, Maxim Boguk wrote: Is there any real reason why checkpoint_timeout limited to 1hour? Just to keep people from accidentally setting a value that's dangerously high. There can be some pretty bad drops in performance if you let writes pile up for too long, once the checkpoint really does start running. In my case I have some replicas with WAL on SAS raid and PGDATA on SSD with limited write enduranceIn that case having checkpoint_timeout=10hour could reduce amout of writes on SSD by factor of 10, and increase planned ssd lifetime by the same amount. The big write endurance problem is WAL data, and you're already addressing that. Note that if nothing has been written out since the last one, the checkpoint won't actually do anything. So this 10X endurance idea might only work out on a system that's always doing something. You'll certainly get less wear; without measuring your workload better, I can't say just what the multiplier is. The other idea you should be considering, if you haven't already, is not provisioning all of the space. I would like to have ability to set checkpoint_timeout=high value and (whats even better) checkpoint_timeout=0 - in that case checkpoint happen when all checkpoint_segments were used. Is there any serious drawbacks in that idea? Is it safe to increase that limit in source and rebuild database? (9.0 and 9.1 case) You can edit src/backend/utils/misc/guc.c , find checkpoint_time, and change the 3600 value there to something higher. You will need to rebuild the whole database cluster with that setting (initdb), and moving a database cluster of files between your tweaked version to/from a regular PostgreSQL will do strange things. You can prevent that from happening accidentally by editing src/include/catalog/catversion.h ; find the line that looks like this: #define CATALOG_VERSION_NO201202141 And change it. It's just MMDDN to create a complete catalog serial number, where N is an incrementing number if more than one change is made on the same day. If you do that and increase the upper bound on checkpoint_timeout, that should do what you want, while protecting against the dangerous situation--where system catalog doesn't match the database binaries. Setting checkpoint_timeout to 0 instead won't work--it will checkpoint all of the time then. The bottom limit is 30 seconds and you don't want to touch that. It's possible to make 0 mean "never timeout", but that would require juggling a couple of code pieces around. The idea of just making the timeout big is a safer thing to consider. I'm not sure if you'll really see the gains you're hoping for, but it should be easy enough to test. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
