Re: [GENERAL] New Zealand Postgis DBA job vacancy
On 25/12/12 09:46, pcr...@pcreso.com wrote: Mat be of interest to someone here... http://careers.eroad.co.nz/vacancies/showVacancy/11 Brent Wood I know some people in Auckland who do PostgreSQL, I've emailed them about it. Curiously, EROAD's head office is about an hours walk from where I live! Cheers, Gavin
Re: [GENERAL] Simple Query Very Slow
Thanks for your responses. Sorry, I forgot to mention that the query actually takes 46 seconds despite what analyze (I dont quite understand the output of explain). We did perform a vacuum last Friday and it seems to help but not too much. We'll also try to recreate the indices. Here's the output of EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM TICKET WHERE CREATED BETWEEN '2012-12-19 00:00:00' AND '2012-12-20 00:00:00' "Index Scan using t_created_idx on ticket (cost=0.00..127638.47 rows=206383 width=183) (actual time=0.065..46104.557 rows=212126 loops=1)" " Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with time zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time zone))" " Buffers: shared hit=44141 read=157167" "Total runtime: 46293.384 ms" Thanks. On Sat, Dec 22, 2012 at 7:26 AM, Andres Freund wrote: > On 2012-12-22 13:06:21 +0100, Alban Hertroys wrote: > > > and here's my query > > > > > > select * from ticket > > > where created between '2012-12-19 00:00:00' and '2012-12-20 00:00:00' > > > > > > This was working fine until the number of records started to grow > (about 5 million) and now it's taking forever to return. > > > > > > Explain analyze reveals this: > > > > > > "Index Scan using ticket_1_idx on ticket (cost=0.00..10202.64 > rows=52543 width=1297) (actual time=0.109..125.704 rows=53340 loops=1)" > > > " Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with > time zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time > zone))" > > > "Total runtime: 175.853 ms" > > > > > Nothing works. What am I doing wrong? why is it selecting sequential > scan? the indexes are supposed to make the query fast. Anything that can be > done to optimize it? > > Whats the time you would need? Beause the above isn't that slow. Perhaps > the timing youre seing from your application includes transferring the > data over a not too fast link? > > It would be interesting to see EXPLAIN (ANALYZE, BUFFERS) $query > > > It is not selecting sequential scan, you're looking at an index scan. > That should be pretty fast, and it isn't that slow - that's still > sub-second performance (0.176s). > > Is that explain from the correct table? According to the results there > are but 53 thousand rows in it, not anywhere near 5 million. > > Well, thats the estimate *after* applying the restriction, so that seems > sensible. > > Greetings, > > Andres Freund > > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
[GENERAL] New Zealand Postgis DBA job vacancy
Mat be of interest to someone here... http://careers.eroad.co.nz/vacancies/showVacancy/11 Brent Wood
Re: [GENERAL] Problem with the semantics of "select into" in a plpgsql function
Thanks Tom, Following David's advice, I've used insert into syntax, but got hit with a report of ambiguity of selected node. The issue was discussed here: http://archives.postgresql.org/pgsql-sql/2011-09/msg00059.php The advice here: http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.htmlhelped me remove the ambiguity, and an insertion of about 900 rows is working 25% or so faster if I remove the loop and use this approach. To clarify: I have a few functions used by a top level function, and one of these is a plpython function that processes a binary blob and creates rows which are inserted into a temp table (created by the top level function). I was trying to make this particular insert faster, which seems to have worked. So everything is going on in a top level pgplsql function with calls to plpython functions at various locations. Regards Seref ps: I am really not sure if there is any other mail list out there that would respond to a technical question with such helpful input on a Christmas day. Thanks guys, you rock! On Tue, Dec 25, 2012 at 6:37 PM, Tom Lane wrote: > Seref Arikan writes: > > I have a plpython function that returns a set of records. > > Is that actually plpython, or plpgsql? Because what you're showing is > not legal syntax in either bare SQL or plpython, but it would act as > you're reporting in plpgsql: > > > SELECT INTO temp_eav_table (valstring, > > featuremappingid, > > featurename, > > rmtypename, > > actualrmtypename, > > path, > > pathstring) > > select selected_node.valstring, > > selected_node.featuremappingid, > > selected_node.featurename, > > selected_node.rmtypename, > > selected_node.actualrmtypename, > > selected_node.path, > > selected_node.pathstring > > from py_get_eav_rows_from_pb(payload ) as selected_node; > > SELECT INTO in plpgsql is a completely different construct than SELECT > INTO in bare SQL: the INTO target is always a local variable of the > function. You should use CREATE TABLE AS to get the effect you're > after. This is covered in the docs page David pointed you to, as > well as in the plpgsql documentation. > > regards, tom lane >
Re: [GENERAL] Problem with the semantics of "select into" in a plpgsql function
Seref Arikan writes: > I have a plpython function that returns a set of records. Is that actually plpython, or plpgsql? Because what you're showing is not legal syntax in either bare SQL or plpython, but it would act as you're reporting in plpgsql: > SELECT INTO temp_eav_table (valstring, > featuremappingid, > featurename, > rmtypename, > actualrmtypename, > path, > pathstring) > select selected_node.valstring, > selected_node.featuremappingid, > selected_node.featurename, > selected_node.rmtypename, > selected_node.actualrmtypename, > selected_node.path, > selected_node.pathstring > from py_get_eav_rows_from_pb(payload ) as selected_node; SELECT INTO in plpgsql is a completely different construct than SELECT INTO in bare SQL: the INTO target is always a local variable of the function. You should use CREATE TABLE AS to get the effect you're after. This is covered in the docs page David pointed you to, as well as in the plpgsql documentation. 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] logger table
Am 25.12.2012 17:19, schrieb Jason Dusek:
2012/12/24 Philipp Kraus :
I need some ideas for creating a PG based logger. I have got a
job, which can run more than one time. So the PK is at the
moment jobid & cycle number. The inserts in this table are in
parallel with the same username from different host
(clustering). The user calls in the executable "myprint" and
the message will insert into this table, but at the moment I
don't know a good structure of the table. Each print call can
be different length, so I think a text field is a good choice,
but I don't know how can I create a good PK value. IMHO a
sequence can be create problems that I'm logged in with the
same user on multiple hosts, a hash key value like SHA1 based
on the content are not a good choice, because content is not
unique, so I can get key collisions. I would like to create
on each "print" call a own record in the table, but how can I
create a good key value and get no problems in parallel
access. I think there can be more than 1000 inserts each
second.
Does anybody can post a good idea?
Why is it neccesry to have a primary key? What is the "cycle
number"?
the cycle number is an increment number starting by 0 til cycle-1
For what it is worth, I put all my syslog in PG and have so far
been fine without primary keys. (I keep only an hour there at a
time, though, and it's only a few hundred megs.)
In the past, I have had trouble maintaining a high TPS while
having lots (hundreds) of connected clients; maybe you'll want
to use a connection pool.
I use a connection pool at the time. I have a MPI process:
for (std::size_t i=0; i < cycle; ++i)
for (std::size_t n=0; n < iterations; ++n)
{
.
log_to_pg_table(i, "log message")
.
mpi::barrier()
}
so the clients are synchronized on each inner loop, the primary key
is also a order number, so message with a previous number get a lower
index like a message that is pushed later to the table. So with a
primary
key I can say, that only the messages within an iteration are
unordered.
--
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] Problem with the semantics of "select into" in a plpgsql function
On 12/25/2012 07:20 AM, Seref Arikan wrote: Greetings, I have a plpython function that returns a set of records. I loop over them to insert them into a temp table created by another function. I wanted to test select into temp_eav_table (column) select a.column from tbl as a where approach to see if it performs better than the loop. However, I'm not able to compile the function due to an error that says "temp_eav_table is not a known variable" Probably going to have to show the actual plpython code. The message looks like you used temp_eav_table as a variable before defining it. So the context assumes this is supposed to be a variable. If I try execute '...', then I have trouble passing a bytea parameter to the python function. This is what I have at the moment: SELECT INTO temp_eav_table (valstring, featuremappingid, featurename, rmtypename, actualrmtypename, path, pathstring) select selected_node.valstring, selected_node.featuremappingid, selected_node.featurename, selected_node.rmtypename, selected_node.actualrmtypename, selected_node.path, selected_node.pathstring from py_get_eav_rows_from_pb(payload ) as selected_node; any thoughts? I think you may be reading the plpgsql docs. The SELECT INTO syntax there only exists within plpgsql. You are using plpythonu and it will not work there. It follows the syntax here: http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html Best regards Seref -- 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] check user in group
On 12/25/2012 01:51 AM, Philipp Kraus wrote: Hello, how can I check if a user is within a group? I use current_user() to get the logged-in user, but I have a group "service" and I need a check if the user is member of the group In recent versions of Postgres user and group have been folded into roles. The terms still exist, to roughly mean user=role with login, group=role without login. There are built in functions to work with roles and privileges. See: http://www.postgresql.org/docs/9.2/interactive/functions-info.html Table 9-51. Access Privilege Inquiry Functions ... pg_has_role(user, role, privilege) boolean does user have privilege for role pg_has_role(role, privilege) boolean does current user have privilege for role There are more available. Thanks Phil -- 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] Problem with the semantics of "select into" in a plpgsql function
"SELECT INTO -- define a new table from the results of a query" If the table exists use: INSERT INTO tbl (.) SELECT . David J. From: Seref Arikan [mailto:serefari...@kurumsalteknoloji.com] Sent: Tuesday, December 25, 2012 10:52 AM To: David Johnston Cc: PG-General Mailing List Subject: Re: [GENERAL] Problem with the semantics of "select into" in a plpgsql function David, Thanks for your response. However, I can't see my solution there. According to documentation, select into is supposed to create a new table. "Create table as " is offered as an alternative, but my table is already ready when I execute the statement. Maybe it is my lack of understanding, but I can't see how the documentation helps to use eav_temp_table as a table name, rather than a variable. On Tue, Dec 25, 2012 at 3:39 PM, David Johnston wrote: The documentation for "select into" covers this and provides your alternatives. http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html David J. On Dec 25, 2012, at 10:20, Seref Arikan wrote: > Greetings, > I have a plpython function that returns a set of records. I loop over them to insert them into a temp table created by another function. > I wanted to test > select into temp_eav_table (column) select a.column from tbl as a where > approach to see if it performs better than the loop. However, I'm not able to compile the function due to an error that says "temp_eav_table is not a known variable" > > So the context assumes this is supposed to be a variable. If I try execute '...', then I have trouble passing a bytea parameter to the python function. This is what I have at the moment: > > SELECT INTO temp_eav_table (valstring, > featuremappingid, > featurename, > rmtypename, > actualrmtypename, > path, > pathstring) > select selected_node.valstring, > selected_node.featuremappingid, > selected_node.featurename, > selected_node.rmtypename, > selected_node.actualrmtypename, > selected_node.path, > selected_node.pathstring > from py_get_eav_rows_from_pb(payload ) as selected_node; > > any thoughts? > > Best regards > Seref >
Re: [GENERAL] logger table
2012/12/24 Philipp Kraus : > I need some ideas for creating a PG based logger. I have got a > job, which can run more than one time. So the PK is at the > moment jobid & cycle number. The inserts in this table are in > parallel with the same username from different host > (clustering). The user calls in the executable "myprint" and > the message will insert into this table, but at the moment I > don't know a good structure of the table. Each print call can > be different length, so I think a text field is a good choice, > but I don't know how can I create a good PK value. IMHO a > sequence can be create problems that I'm logged in with the > same user on multiple hosts, a hash key value like SHA1 based > on the content are not a good choice, because content is not > unique, so I can get key collisions. I would like to create > on each "print" call a own record in the table, but how can I > create a good key value and get no problems in parallel > access. I think there can be more than 1000 inserts each > second. > > Does anybody can post a good idea? Why is it neccesry to have a primary key? What is the "cycle number"? For what it is worth, I put all my syslog in PG and have so far been fine without primary keys. (I keep only an hour there at a time, though, and it's only a few hundred megs.) In the past, I have had trouble maintaining a high TPS while having lots (hundreds) of connected clients; maybe you'll want to use a connection pool. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B -- 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] Problem with the semantics of "select into" in a plpgsql function
David, Thanks for your response. However, I can't see my solution there. According to documentation, select into is supposed to create a new table. "Create table as " is offered as an alternative, but my table is already ready when I execute the statement. Maybe it is my lack of understanding, but I can't see how the documentation helps to use eav_temp_table as a table name, rather than a variable. On Tue, Dec 25, 2012 at 3:39 PM, David Johnston wrote: > The documentation for "select into" covers this and provides your > alternatives. > > http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html > > David J. > > > On Dec 25, 2012, at 10:20, Seref Arikan > wrote: > > > Greetings, > > I have a plpython function that returns a set of records. I loop over > them to insert them into a temp table created by another function. > > I wanted to test > > select into temp_eav_table (column) select a.column from tbl as a > where > > approach to see if it performs better than the loop. However, I'm not > able to compile the function due to an error that says "temp_eav_table is > not a known variable" > > > > So the context assumes this is supposed to be a variable. If I try > execute '...', then I have trouble passing a bytea parameter to the python > function. This is what I have at the moment: > > > > SELECT INTO temp_eav_table (valstring, > > featuremappingid, > > featurename, > > rmtypename, > > actualrmtypename, > > path, > > pathstring) > > select selected_node.valstring, > > selected_node.featuremappingid, > > selected_node.featurename, > > selected_node.rmtypename, > > selected_node.actualrmtypename, > > selected_node.path, > > selected_node.pathstring > > from py_get_eav_rows_from_pb(payload ) as selected_node; > > > > any thoughts? > > > > Best regards > > Seref > > >
Re: [GENERAL] Problem with the semantics of "select into" in a plpgsql function
The documentation for "select into" covers this and provides your alternatives. http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html David J. On Dec 25, 2012, at 10:20, Seref Arikan wrote: > Greetings, > I have a plpython function that returns a set of records. I loop over them to > insert them into a temp table created by another function. > I wanted to test > select into temp_eav_table (column) select a.column from tbl as a where > approach to see if it performs better than the loop. However, I'm not able to > compile the function due to an error that says "temp_eav_table is not a known > variable" > > So the context assumes this is supposed to be a variable. If I try execute > '...', then I have trouble passing a bytea parameter to the python function. > This is what I have at the moment: > > SELECT INTO temp_eav_table (valstring, > featuremappingid, > featurename, > rmtypename, > actualrmtypename, > path, > pathstring) > select selected_node.valstring, > selected_node.featuremappingid, > selected_node.featurename, > selected_node.rmtypename, > selected_node.actualrmtypename, > selected_node.path, > selected_node.pathstring > from py_get_eav_rows_from_pb(payload ) as selected_node; > > any thoughts? > > Best regards > Seref > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with the semantics of "select into" in a plpgsql function
Greetings, I have a plpython function that returns a set of records. I loop over them to insert them into a temp table created by another function. I wanted to test select into temp_eav_table (column) select a.column from tbl as a where approach to see if it performs better than the loop. However, I'm not able to compile the function due to an error that says "temp_eav_table is not a known variable" So the context assumes this is supposed to be a variable. If I try execute '...', then I have trouble passing a bytea parameter to the python function. This is what I have at the moment: SELECT INTO temp_eav_table (valstring, featuremappingid, featurename, rmtypename, actualrmtypename, path, pathstring) select selected_node.valstring, selected_node.featuremappingid, selected_node.featurename, selected_node.rmtypename, selected_node.actualrmtypename, selected_node.path, selected_node.pathstring from py_get_eav_rows_from_pb(payload ) as selected_node; any thoughts? Best regards Seref
[GENERAL] dataset user access
Hello, I need a idea to solve the following problem: I have a table which datasets are owned by th pg login user. If the pg user is deleted, the owner of the dataset should be set to null, that means that only a super user can access to the dataset. If the username is renamed the owner of the dataset is also renamed. I have found, that I can not setup a constraint to the pg_auth or to pg_user. The next problem I see, if I delete a user and add a new user with an equal username, the new user get access to the datasets of the old user. Can I create a reference from a table to the pg system table structurs, so that I get a "unique" user id and if the user id is removed, the field in my table is set to null? Thanks Phil -- 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] check user in group
Hello
you can try
create or replace function is_member_of(text, text) returns boolean as $$
select exists(select rolname from pg_catalog.pg_auth_members m JOIN
pg_catalog.pg_roles b ON m.roleid = b.oid where m.member = (select oid
from pg_roles where rolname = $1) and rolname = $2)
$$ language sql ;
postgres=# select is_member_of('pavel','admin');
is_member_of
--
t
(1 row)
Regards
Pavel Stehule
2012/12/25 Philipp Kraus :
> Hello,
>
> how can I check if a user is within a group? I use current_user() to get the
> logged-in user, but I have a group "service" and I need a check if the user
> is member of the group
>
> Thanks
>
> Phil
>
> --
> 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] check user in group
Hello, how can I check if a user is within a group? I use current_user() to get the logged-in user, but I have a group "service" and I need a check if the user is member of the group Thanks Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
