[GENERAL] Composite types or composite keys?
Hi, I was reading about composite types and wondering if I should use them instead of composite keys. I currently have tables like this: create table products ( source_system text, product_id text, description text, ... primary key (source_system, product_id) ); create table inventory ( source_system text, product_id text, qty int, ... foreign key (source_system, product_id) references products ); and it means having to add the “source_system column to many queries. Would something like: create type product as ( source_system text, product_id text ); create table products ( product product, description text, ... primary key(product) ); create table inventory ( product product, qty numeric, ... foreign key (product) references products ); be a correct use of composite types? I rarely need to see the columns separately, so having to write “(product).product_id” won’t happen much in practice. Thanks, Tony -- 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 Server backend process
Hello, I was going through http://www.postgresql.org/files/developer/tour.pdf and the source at http://doxygen.postgresql.org/postmaster_8c_source.html The pdf shows the daemon process doing authentication and spawning a process to handle the request from the client. The code has these comments - When a request message is received, we now fork() immediately. The child process performs authentication of the request, Now authentication is done by the Backend process and not by the daemon? Regards, Jayadevan
Re: [GENERAL] pg_trgm module: no convertion into Trigrams on one side when comparing
Hi Amit, Do you think it would by difficult to edit the source and wirte a new function, wich does it? Janek Sendrowski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Server backend process
Jayadevan M wrote: The code has these comments - When a request message is received, we now fork() immediately. The child process performs authentication of the request, Now authentication is done by the Backend process and not by the daemon? Yes. The authentication is called in InitPostgres(), which is called in PostgresMain(). 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
[GENERAL] counterpart to LOAD
Hi @all, ist there (in 9.1) a way to unload a shared lib? It seems it's impossible since 8.1 or so, i'm right? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Install pg_trgm from source
Hi, I like to change the source code of the pg_trgm extension a little bit. Where can I get the source code and how do I compile it? I can only find the SQL-files of pg_trgm in my directories. Janek Sendowksi
Re: [GENERAL] counterpart to LOAD
Andreas Kretschmer wrote: ist there (in 9.1) a way to unload a shared lib? It seems it's impossible since 8.1 or so, i'm right? Yes: http://www.postgresql.org/docs/9.3/static/xfunc-c.html#XFUNC-C-DYNLOAD (Presently, unloads are disabled and will never occur, but this may change in the future.) This thread might be interesting: http://www.postgresql.org/message-id/e94e14cd0912231317w441fad87gb3a4c517603a3...@mail.gmail.com 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] Install pg_trgm from source
On Fri, Nov 15, 2013 at 10:00 PM, Janek Sendrowski jane...@web.de wrote: Hi, I like to change the source code of the pg_trgm extension a little bit. Where can I get the source code and how do I compile it? If you have fetched a tarball of Postgres or git copy, simply have a look in contrib/pg_trgm. Regards, -- Michael -- 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] Install pg_trgm from source
Thanks a lot for your Answers. That's what I was looking for. I couln't find it. -- 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] Composite types or composite keys?
On Fri, Nov 15, 2013 at 2:01 AM, Tony Theodore tony.theod...@gmail.com wrote: Hi, I was reading about composite types and wondering if I should use them instead of composite keys. I currently have tables like this: create table products ( source_system text, product_id text, description text, ... primary key (source_system, product_id) ); create table inventory ( source_system text, product_id text, qty int, ... foreign key (source_system, product_id) references products ); and it means having to add the “source_system column to many queries. Would something like: create type product as ( source_system text, product_id text ); create table products ( product product, description text, ... primary key(product) ); create table inventory ( product product, qty numeric, ... foreign key (product) references products ); be a correct use of composite types? I rarely need to see the columns separately, so having to write “(product).product_id” won’t happen much in practice. Well, here are the downsides. Composite types: *) are more than the sum of their parts performance-wise. So there is a storage penalty in both the heap and the index *) can't leverage indexes that are querying only part of the key *) will defeat the implicit 'per column NOT NULL constraint' of the primary keys *) are not very well supported in certain clients -- for example JAVA. you can always deal with them as text, but that can be a headache. ...plus some other things I didn't think about. If you can deal with those constraints, it might be interesting to try a limited experiment. The big upside of composite types is that you can add attributes on the fly without rebuilding the index. Test carefully. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Push predicate down in view containing window function
On Fri, Nov 15, 2013 at 12:43 AM, Philippe Girolami philippe.girol...@sensorly.com wrote: Ok so is there a way i can do something similar ? Would a function returning rows and taking the extra predicate 'values' as parameters be as optimized as the 'good' query in my first email ? There is only one way I know of to do it (force a qual into a view wrapped into a subquery). It's tweaky, particularly with pre-9.3 LATERAL. The basic MO is to put the window function into SQL function in order to be able to force the qual into the inner query with a function parameter. Then, you make a view that cross products the possible arguments to the function and LATERALS them into the set returning function (it's possible, but difficult, to do it without lateral). As long as the 'arguments' expressed in the view are always specified in the query that hits the view performance should be good. If this sounds like something you'd like to tackle, maybe I can work up an example. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGSQL: listing db/role and user/role relationships
Hi all, How can I retrieve: 1) each role’s privileges on a given DB For example, if I do: “GRANT ALL PRIVILEGES ON DATABASE thedb TO therole”, how can I list this grant among others with access to “thedb”? 2) which users have access to a given role For example, if I do: “GRANT therole TO theuser”, how can I list all of the users that can take on “therole”? 3) which roles a given user can access For example, if I do: “GRANT therole TO theuser”, how can I list all of the roles that “theuser” can take on? Thanks! -Felipe Gasper Houston, TX, USA -- 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] Partitioned table question
On 11/13/2013 06:22 AM, Torsten Förtsch wrote: Hi, we have a table partitioned by time. Each month goes into a separate child table. Primary key in each table is (underlying, ts). The resulting index is perfect for ordering like in the query below. Each child table has a constraint like: CHECK(ts= '2011-1-1' and ts'2011-1-1'::DATE + interval '1 month') Now, we have queries of this type: SELECT * FROM tick WHERE underlying = 'R_50' AND ts = '2013-05-02' ORDER BY ts DESC LIMIT 100 In the query plan the condition shown is ... AND ts = '2013-05-01' Did you mean 01 in the above query? The query plan for this is at http://explain.depesz.com/s/fB6 According to this plan it fetches all the result tuples from tick_2013_4 which is fine because tick_2013_5 obviously does not contain matches. Since the constraint is not strict (i.e. you allow dates equal to 2013-05-01 to pass), the 2013-05 table has to be scanned. My question is, why does it then try to fetch one row from every other index? Can that be avoided without a lower bound on ts? If you don't set a lower bound, since every other table has dates below 2013-05-01, they have to be scanned too. I'm not sure what happens on actual execution if it searches in '2013_4' first and finds 100 or more rows. I don't know if the query planner uses constraint exclusion rules to figure out the order in which tables will be scanned. I suspect not, because I've read and seen that the constraint exclusion rules behavior is rather simple. If you set a lower bound the constraint exclusion rule should kick in and limit the tables searched. Have you tried that? Thanks, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Save many data chunks to file
Hello! I have a table with image data: /CREATE TABLE images// //(// //id serial,// //image_data bytea,// //...// //);/ This is function for store image data to file: /CREATE OR REPLACE FUNCTION write_bytea ( p_data bytea, p_filename text )// //RETURNS void AS// //$BODY$// //DECLARE// //v_oid oid;// //v_fdesc integer := 0;// //v_fsize integer := 0;// //BEGIN// //v_oid := lo_create ( -1 );// //v_fdesc := lo_open ( v_oid, CAST ( X'0002' AS integer ) );// //v_fsize := lowrite ( v_fdesc, p_data );// //PERFORM lo_export ( v_oid, p_filename );// //PERFORM lo_close ( v_fdesc );// //PERFORM lo_unlink ( v_oid );// //END// //$BODY$// //LANGUAGE plpgsql IMMUTABLE;/ Query for saving image: /SELECT write_bytea ( i.image_data, id::text || '.jpg' )// //FROM images i;/ My problem: first 30-40 images (~75 KB for one image) save fast but saving speed of nextimages slows down and slows down. What is wrong? P.S. I try use it in pgAdmin and in psql, but problem doesn't disappear.
[GENERAL] Postgresql Service and Server synch up issue.
Hi All,
We are using postgreql 9.2 as a main backend server for our project. We have
been using this server since last year. We have configured postgresql as a
windows service. Till last week, we have not faced any issues to start server
from service console management, but last week we have faced a issue that when
server was started , service was timed out and pg_ctl.exe closed, while
rebooting.
This issue is faced by few people as described in below links.
http://www.postgresql.org/message-id/flat/e1usoey-00024n...@wrigleys.postgresql.org#e1usoey-00024n...@wrigleys.postgresql.org
http://postgresql.1045698.n5.nabble.com/Unreliable-quot-pg-ctl-w-start-quot-again-td5435767.html
Based on the links, I have analyzed the pg_ctl.c code and found that.
write_eventlog(EVENTLOG_INFORMATION_TYPE, _(Waiting for server startup...\n));
if (test_postmaster_connection(true) != PQPING_OK)
{
write_eventlog(EVENTLOG_ERROR_TYPE, _(Timed out waiting for server
startup\n));
pgwin32_SetServiceStatus(SERVICE_STOPPED);
return;
}
test_postmaster_connection(bool do_checkpoint)
{
...
...
...
for (i = 0; i wait_seconds; i++)
{
...
...
if (i = 5)
{
struct stat statbuf;
if (stat(pid_file, statbuf) != 0)
return PQPING_NO_RESPONSE;
if (found_stale_pidfile)
{
write_stderr(_(\n%s: this data directory appears to be
running a pre-existing postmaster\n),
progname);
return PQPING_NO_RESPONSE;
}
}
}
It is checking only 5 seconds, whether postmaster.pid is available or not. If
not then it will send Timed out waiting for server startup log and followed
by this log The Postgres service entered the stopped state.
As per the link, I can change 5 to 20 or some value to avoid timeout.
Please help me here, how can I resolve this issue in a better way.
Thanks Regards,
Ramkumar.
Re: [GENERAL] Add COPY statement inside sql function AND/OR call function within function
On 11/12/2013 12:56 PM, Johannes Björk wrote:
Hi, Im hoping someone could help me with this. I am new to any kind of
sql coding so bare with me.
I have written the below working function which I would like to print to
.csv file(s)
|CREATE FUNCTION retrieve_info(input_method TEXT, input_species TEXT)
RETURNS SETOF
retrieve_info_tblAS $$
SELECT tblA.id, tblA.method, tblA.species, tblA.location
FROM tblA
WHERE method=input_methodAND species=input_species
GROUP BY id, method, species
ORDER BY location
$$ LANGUAGE'sql';|
*DUMMY DATA*
tblA (filled)
|create table tblA(id varchar(5) PRIMARY KEY, method text, species
varchar(10), location
text);
insert into tblAvalues ('1a', 'mtd1', 'sp1', 'locA'),('1b', 'mtd1',
'sp2', 'locC'),('1c',
'mtd2', 'sp3', 'locB'),('1d', 'mtd1', 'sp1', 'locB'),('1e', 'mtd2',
'sp5', 'locA');|
retrieve_info_tbl (empty)
|create table retrieve_info_tbl(id varchar(5) PRIMARY KEY, method text,
ind varchar(10),
location text);|
Calling function
|SELECT * FROM retrieve_info('mtd1','sp1');|
*OUTPUT*
|retrieve_info(mtd1, sp3)
id| method| ind| location
1a| mtd1| sp3| locA
1d| mtd1| sp3| locB|
Since I have not succeeded in this, I tried to work around it creating a
function which called this function and printed the result to a .csv file.
It looks like it is succeeding, it returns a setof. What are you looking
to do?
|CREATE FUNCTION print_out(x TEXT, y TEXT) RETURNS voidAS $$
COPY(SELECT * FROM retrieve_info(x,y)) TO 'myfilepath/test.csv'
WITH CSV HEADER;
$$ LANGUAGE'sql';|
Calling nested function.
|SELECT * FROM print_out('mtd1','sp1');|
|
*OUTPUT*
The above gives this |ERROR: column x does not exist SQL state: 42703
Context: SQL function print_out statement 1|. However, when
substituting x,y in print_out() with 'mtd1','sp1' the correct output is
printed to test.csv
I would really appreciate any pointers on either one of the above problems.
Many thanks,
Johannes
|
--
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] log_line_prefix
Hi, I was trying different options of log_line_prefix. I am making chnages, doing a pg_ctl reload and checking the output in the log files. For some reason ,the changes seem to have no impact. What am I doing wrong? Here is the output form the log files where you can see the change being accepted (reload) and then the output of a select now(). 2013-11-16 08:24:50.657 IST,,,3186,,5286c0d6.c72,5,,2013-11-16 06:18:22 IST,,0,LOG,0,received SIGHUP, reloading configuration files, 2013-11-16 08:24:50.657 IST,,,3186,,5286c0d6.c72,6,,2013-11-16 06:18:22 IST,,0,LOG,0,parameter log_line_prefix changed to %t , 2013-11-16 08:24:56.805 IST,postgres,postgres,3460,[local],5286de7e.d84,1,SELECT,2013-11-16 08:24:54 IST,2/0,0,LOG,0,duration: 1.623 ms statement: select now();,psql 2013-11-16 08:25:49.376 IST,,,3186,,5286c0d6.c72,7,,2013-11-16 06:18:22 IST,,0,LOG,0,received SIGHUP, reloading configuration files, 2013-11-16 08:25:49.378 IST,,,3186,,5286c0d6.c72,8,,2013-11-16 06:18:22 IST,,0,LOG,0,parameter log_line_prefix changed to %m , 2013-11-16 08:25:53.384 IST,postgres,postgres,3464,[local],5286deb7.d88,1,SELECT,2013-11-16 08:25:51 IST,2/0,0,LOG,0,duration: 0.978 ms statement: select now();,psql 2013-11-16 08:27:28.348 IST,,,3186,,5286c0d6.c72,9,,2013-11-16 06:18:22 IST,,0,LOG,0,received SIGHUP, reloading configuration files, 2013-11-16 08:27:28.349 IST,,,3186,,5286c0d6.c72,10,,2013-11-16 06:18:22 IST,,0,LOG,0,parameter log_line_prefix changed to %m %d %u , 2013-11-16 08:27:34.681 IST,postgres,postgres,3469,[local],5286df1d.d8d,1,SELECT,2013-11-16 08:27:33 IST,2/0,0,LOG,0,duration: 0.732 ms statement: select now();,psql I think some other setting is printing all the info anyway. But which setting? Regards, Jayadevan
[GENERAL] Re: Add COPY statement inside sql function AND/OR call function within function
Johannes Björk wrote
Hi, Im hoping someone could help me with this. I am new to any kind of sql
coding so bare with me.
I have written the below working function which I would like to print to
.csv file(s)
CREATE FUNCTION retrieve_info(input_method TEXT, input_species TEXT)
RETURNS SETOF
retrieve_info_tbl AS $$
SELECT tblA.id, tblA.method, tblA.species, tblA.location
FROM tblA
WHERE method=input_method AND species=input_species
GROUP BY id, method, species
ORDER BY location
$$ LANGUAGE 'sql';
DUMMY DATA
tblA (filled)
create table tblA (id varchar(5) PRIMARY KEY, method text, species
varchar(10), location
text);
insert into tblA values ('1a', 'mtd1', 'sp1', 'locA'),('1b', 'mtd1',
'sp2', 'locC'),('1c',
'mtd2', 'sp3', 'locB'),('1d', 'mtd1', 'sp1', 'locB'),('1e', 'mtd2', 'sp5',
'locA');
retrieve_info_tbl (empty)
create table retrieve_info_tbl (id varchar(5) PRIMARY KEY, method text,
ind varchar(10),
location text);
Calling function
SELECT * FROM retrieve_info('mtd1','sp1');
OUTPUT
retrieve_info(mtd1, sp3)
id | method | ind | location
1a | mtd1 | sp3 | locA
1d | mtd1 | sp3 | locB
Since I have not succeeded in this, I tried to work around it creating a
function which called this function and printed the result to a .csv file.
CREATE FUNCTION print_out(x TEXT, y TEXT) RETURNS void AS $$
COPY (SELECT * FROM retrieve_info(x,y)) TO 'myfilepath/test.csv'
WITH CSV HEADER;
$$ LANGUAGE 'sql';
Calling nested function.
SELECT * FROM print_out('mtd1','sp1');
OUTPUT
The above gives this ERROR: column x does not exist SQL state: 42703
Context: SQL function print_out statement 1. However, when substituting
x,y in print_out() with 'mtd1','sp1' the correct output is printed to
test.csv
I would really appreciate any pointers on either one of the above
problems.
Many thanks,
Johannes
What PostgreSQL version?
SQL functions only recently could refer to input parameters by name. Before
you had to use $1, $2, etc... To reference them.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Add-COPY-statement-inside-sql-function-AND-OR-call-function-within-function-tp5778666p5778683.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
