Re: [GENERAL] Recovery from Archive files
On 10/19/11 9:31 PM, senthilnathan wrote: Thanks John., Yes its from WAL Archives. I dont have the basebackup instead i am having all the WAL files that are archived right from the server start. My question is how(steps?) to to build whole setup(postgres server)by replaying all WAL files WAL file archives are like successive differences from the base backup. without the base backup, they are useless. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] archive_timeout runtime change
Hi I use postgrsql 9.1.1 and I want to change archive_timeout on a running server. My default archive_timeout is 1800s, and nightly I want to change to 3600s or more, but I get the following error message: set archive_timeout='3600'; ERROR: parameter archive_timeout cannot be changed now and update pg_settings set setting='900' where name='archive_timeout'; ERROR: parameter archive_timeout cannot be changed now I know is possible to change this parameter with pg_ctl reload, but I think if is possible without changing the conf file. Thanks Levi -- 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] Recovery from Archive files
On Thu, Oct 20, 2011 at 10:01 AM, senthilnathan senthilnatha...@gmail.comwrote: Thanks John., Yes its from WAL Archives. I dont have the basebackup instead i am having all the WAL files that are archived right from the server start. My question is how(steps?) to to build whole setup(postgres server)by replaying all WAL files (from 00010001000100010027) I think you should give a try on any test box. Am assuming you are having $PGDATA (data directory) and their WAL Archives. Take a copy of these to your test box and follow below steps. 1. Remove postmaster.pid from $PGDATA 2. Create recovery.conf file in $PGDATA directory. vi recovery.conf restore_command = ' cp /WAL Archives destination/%f %p' recovery_target_time = '2011-10-20 14:30:25' :wq Note: Here target_time is your current rebuild time, because you want to replay all WAL Archives which are available. 3. Start the instance and check $PGDATA/pg_log/.log file on to know how recovery process went. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] psqlODBC connection
On 20/10/2011 05:23, mamatha_kagathi_c...@dell.com wrote: Hi Ray, Thanks for the help. Since I am using classic ASP this is exactly what I am looking for. But there is a problem here. When I go the Create New Data Source window as mentioned in step ii, Ifind only sql server and Oracle drivers. I do not see PostgreSQL ODBC driver. But I am sure PsqlODBC is installed as it appears in the installed program list. Is there anything else I am missing, because of which the driver does not appear in the source?? Please let me know. Hi Mamatha, As Edson said upthread, you might have installed the 32-bit driver on 64-bit Windows, in which case you have to use the 32-bit ODBC manager. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Date Range Using Months and Days Only
On Thu, Oct 20, 2011 at 8:42 AM, Jeff Adams jeff.ad...@noaa.gov wrote: Thanks for the reply and assistance. I share your concern that the approach may be slow. I am not adverse to creating some sort of table to store the time periods with years for the temporal extent of the data if that would speed up the process. In that situation there would be multiple records (one record for each year) that it would need to check against. I suppose I could then create some sort of aggregate to then count the number of records in which the date falls within the range. Anything over 0 would indicate that the date does fall within the range? From: Adam Cornett [mailto:adam.corn...@gmail.com] Sent: Wednesday, October 19, 2011 6:51 PM To: Jeff Adams Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Date Range Using Months and Days Only On Wed, Oct 19, 2011 at 12:40 PM, Jeff Adams jeff.ad...@noaa.gov wrote: Greetings, I have to write a query on a fairly large table of data (100 million rows) where I need to check to see if a date (epoch) falls between a range of values. The catch is that the range is defined only by month and day values. For example the record containing the epoch value will be linked to a table that containing columns named start_month, start_day, end_month, end_day that define the range. With respect to the range, year does not matter, however, some of the ranges will start in November and end in April of the next year. Has anyone come across this type of query? I could certainly write a function or even include criteria in a query that would extract date parts of the epoch and then compare against the values in the start_month, start_day, end_month, end_day (it might get complex with respect to ranges where the start year and end year are different), but I am worried about performance. I thought I would seek some input before I floundered through the many iterations of poor performing alternatives! Any thoughts would be greatly appreciated. Thanks in advance... Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general create table a ( id_a integer, epoch integer ); create table b ( id_b integer, start_month integer, start_day integer, end_month integer, end_day integer ); CREATE OR REPLACE FUNCTION get_timestamps(sm integer, sd integer, em integer, ed integer, year integer) RETURNS timestamp without time zone[] AS $BODY$ declare syear integer := year; eyear integer := year; tstamps timestamp[]; begin if(smem) then -- assume that since the end month is less than the start month is in the next year eyear := eyear+1; end if; tstamps[0] := (syear|| '-' || sm || '-' || sd)::timestamp; tstamps[1] := (eyear|| '-' || em || '-' || ed)::timestamp; return tstamps; end $BODY$ LANGUAGE plpgsql STABLE; create view a_timestamp as SELECT id_a, TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second' as tstamp from a; with ab as( select id_a,id_b,get_timestamps(b.start_month,b.start_day,b.end_month,b.end_day, extract(year from a.tstamp)::integer) ts_arr,a.tstamp from a_timestamp as a,b ) select * from ab where ab.tstamp between ts_arr[0] and ts_arr[1] This obviously isn't a fast solution to your problem, although converting the integer epoch to a timestamp in table a would eliminate the view a_timestamp and you can index the column for some speed up, the real problem you're facing is that your ranges don't have years, otherwise you could store everything as a timestamp and then just join using 'between' and postgres would just need to do an index scan on each table. -Adam Here is an updated method, it involves creating a third table to store the timestamp values from table 'b' to allow for better lookups, I've also added a timestamp column to table 'a' (which can be calculated from the epoch using the view in my previous email. create table a ( id_a integer, epoch integer, ts timestamp ); CREATE INDEX ON a (ts); create table b ( id_b integer, start_month integer, start_day integer, end_month integer, end_day integer ); CREATE OR REPLACE FUNCTION get_timestamps(sm integer, sd integer, em integer, ed integer, year integer) RETURNS timestamp without time zone[] AS $BODY$ declare syear integer := year; eyear integer := year; tstamps timestamp[]; begin if(smem) then -- assume that since the end month is less than the start month is in the next year eyear := eyear+1; end if; tstamps[0] := (syear|| '-' || sm || '-' || sd)::timestamp; tstamps[1] := (eyear|| '-' || em || '-' || ed)::timestamp; return tstamps; end $BODY$ LANGUAGE plpgsql STABLE; create table b_calc ( id_b integer, year integer, range_start timestamp, range_end timestamp ); CREATE
Re: [GENERAL] timeline X of the primary does not match recovery target timeline Y
On Mon, 2011-09-26 at 08:54 -0400, Adam Tistler wrote: I am trying to set up master/slave warm standby streaming replication (9.1). I am not doing archiving/log shipping. I have read that you cannot reverse roles between the master and slave, and that once a slave is promoted, you need to use pg_basebackup or rsync to copy files from the newly promoted master to the old master. I am fine with this, however, in my case all I am trying to do is re-enable the slave to be in recovery mode. I am doing this by deleting the trigger file ( which I had originally created to promote the slave to master ) and moving recovery.done to recovery.conf, then restarting postgres. As a result I get the following error: FATAL: timeline 2 of the primary does not match recovery target timeline 3 Once it's promoted to a primary, you can't set it to start recovering from another system again (without taking a new base backup). Did I understand your question correctly? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql - FDW, ForeignScanState and subqueries
Hello. Sorry if this is not the appropriate mailing list, but I understood that I needed to post here first. Warning: I'm not a native english speaker ;) I'm implementing a Foreign Data Wrapper, and I'm trying to optimize it by parsing the required columns and simple quals from the foreignscanstate. Let's suppose I have a table defined like this: create foreign table test ( name character varying, value character varying, bigcolumn bytea ) Populated like this: name | value |bigcolumn --++-- test2 | 4 | \x72616e646f6d737472696e676f666279746573 test1 | 1 | \x72616e646f6d737472696e676f666279746573 test2 | 2 | \x72616e646f6d737472696e676f666279746573 test2 | 3 | \x72616e646f6d737472696e676f666279746573 (4 lignes) Now, if I query my table like this (a subquery, joined on the outer query), what info should I be able to parse from the PlanState ? select name, (select max(value) from test t2 where t2.name = t1.name) as max from test t1; I don't really know much about postgresql internals, regarding execution plans, but here is what I guessed from what I managed to extract from the plan tree so far: - The outer query is executed once, restricting only the needed columns - The subquery is executed once for each row, with: - all columns from the table are requested in a target entry node, even if the query only need the name and value columns. - the value corresponding to the name from the outer query is somehow passed in as an Expr of type T_Param, in the quals field. How can I retrieve the value from the Param struct ? The source does not help me much with what to do regarding the various fields in the struct. Does postgresql really fetch all columns in a subselect, or am I just parsing the tree in a wrong way ? I've tried to look at the oracle-fdw code, but I don't understand what they are doing with params. Thank you ! -- Ronan Dunklau
Re: [GENERAL] timeline X of the primary does not match recovery target timeline Y
Yes you understood correctly. Would the same apply for a hot standby situation? On Oct 20, 2011, at 12:00 PM, Jeff Davis wrote: On Mon, 2011-09-26 at 08:54 -0400, Adam Tistler wrote: I am trying to set up master/slave warm standby streaming replication (9.1). I am not doing archiving/log shipping. I have read that you cannot reverse roles between the master and slave, and that once a slave is promoted, you need to use pg_basebackup or rsync to copy files from the newly promoted master to the old master. I am fine with this, however, in my case all I am trying to do is re-enable the slave to be in recovery mode. I am doing this by deleting the trigger file ( which I had originally created to promote the slave to master ) and moving recovery.done to recovery.conf, then restarting postgres. As a result I get the following error: FATAL: timeline 2 of the primary does not match recovery target timeline 3 Once it's promoted to a primary, you can't set it to start recovering from another system again (without taking a new base backup). Did I understand your question correctly? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timeline X of the primary does not match recovery target timeline Y
On Thu, 2011-10-20 at 12:45 -0400, Adam Tistler wrote: Yes you understood correctly. Would the same apply for a hot standby situation? Right. Hot standby just means that you can query (read-only) an instance that's still in recovery. Basically, going from recovery mode (or hot standby, which is also recovery mode) to up (that is, can accept write queries and operate normally) is not a reversible process. You have to make a new base backup of another system to start recovering again. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extraneous Files
If someone happened to accidentally end up with a lot of files that were NOT part of their database in the data/base/X directory, how could they go about getting a reliable list of files they could safely delete? The files were there before the current incarnation of the database, so have ctime before the good ones, but may be valid because they were updated by rsync, but I don't know that mtime is reliable for every file either, since some may have been unchanged from the first time this someone ran rsync. Not a super big deal, just wasting a lot of space... Thanks! Ian postgresql 9.0.4 on linux -- 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] inserting bytea using PHPs pg_escape_bytea()
El día 19 de octubre de 2011 23:20, Jeff Davis pg...@j-davis.com escribió:
On Wed, 2011-10-19 at 14:30 -0300, Martín Marqués wrote:
The only concern I have is that on insertion, I get this WARNING:
WARNING: nonstandard use of \\ in a string literal at character 41
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
Should I worry? What does it mean?
First of all, the best solution is to use parameterized queries:
http://us.php.net/manual/en/function.pg-query-params.php
How would that work with abstraction layers like MDB2 or PDO?
But here's the explanation for the warning:
Check the settings for:
SHOW standard_conforming_strings;
SHOW escape_string_warning;
I assume that those are false and true respectively. If that's the case,
you are safe, HOWEVER it means that you are using non-standard literals.
They are exactly that way.
It's advisable to move to standard string literals (that is, as the SQL
spec defines them) because if you port your application to other systems
in the future, or if you later turn standard_conforming_strings to TRUE,
then you could be vulnerable to SQL injection.
The only place I get these messages are when inserting (or updateing)
bytea columns with images (normally jpeg and png).
This is done in this way:
$foto = file_get_contents($myFile);
$escapado = pg_escape_bytea($foto);
// $db is a MDB2 object conecting to PG
$db-exec(INSERT INTO fotos VALUES ('{$escapado}'));
To become standards-compliant, set standard_conforming_strings to TRUE,
and pg_escape_bytea should automatically start working in the standard
way. It is advisable to explicitly pass the connection object (first
parameter) to pg_escape_bytea() to make sure no mistakes are made. Try
it out with a few test strings to make sure it's using the correct
escaping, see:
OK, so I'd have to do something like:
$escapado = pg_escape_bytea($db-connection, $foto);
But setting standard_conforming_strings to TRUE first.
If I don't change the value of standard_conforming_strings, what does
pg_escape_bytea do different?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
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] inserting bytea using PHPs pg_escape_bytea()
El 20/10/11 14:13, Martín Marqués escribió:
El día 19 de octubre de 2011 23:20, Jeff Davispg...@j-davis.com escribió:
On Wed, 2011-10-19 at 14:30 -0300, Martín Marqués wrote:
The only concern I have is that on insertion, I get this WARNING:
WARNING: nonstandard use of \\ in a string literal at character 41
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
Should I worry? What does it mean?
First of all, the best solution is to use parameterized queries:
http://us.php.net/manual/en/function.pg-query-params.php
How would that work with abstraction layers like MDB2 or PDO?
With PDO just check http://www.php.net/manual/en/pdo.prepare.php
But here's the explanation for the warning:
Check the settings for:
SHOW standard_conforming_strings;
SHOW escape_string_warning;
I assume that those are false and true respectively. If that's the case,
you are safe, HOWEVER it means that you are using non-standard literals.
They are exactly that way.
It's advisable to move to standard string literals (that is, as the SQL
spec defines them) because if you port your application to other systems
in the future, or if you later turn standard_conforming_strings to TRUE,
then you could be vulnerable to SQL injection.
The only place I get these messages are when inserting (or updateing)
bytea columns with images (normally jpeg and png).
This is done in this way:
$foto = file_get_contents($myFile);
$escapado = pg_escape_bytea($foto);
// $db is a MDB2 object conecting to PG
$db-exec(INSERT INTO fotos VALUES ('{$escapado}'));
To become standards-compliant, set standard_conforming_strings to TRUE,
and pg_escape_bytea should automatically start working in the standard
way. It is advisable to explicitly pass the connection object (first
parameter) to pg_escape_bytea() to make sure no mistakes are made. Try
it out with a few test strings to make sure it's using the correct
escaping, see:
OK, so I'd have to do something like:
$escapado = pg_escape_bytea($db-connection, $foto);
But setting standard_conforming_strings to TRUE first.
If I don't change the value of standard_conforming_strings, what does
pg_escape_bytea do different?
Re: [GENERAL] Postgresql - FDW, ForeignScanState and subqueries
Ronan Dunklau rdunk...@gmail.com writes: Now, if I query my table like this (a subquery, joined on the outer query), what info should I be able to parse from the PlanState ? select name, (select max(value) from test t2 where t2.name = t1.name) as max from test t1; I don't really know much about postgresql internals, regarding execution plans, but here is what I guessed from what I managed to extract from the plan tree so far: - The outer query is executed once, restricting only the needed columns - The subquery is executed once for each row, with: - all columns from the table are requested in a target entry node, even if the query only need the name and value columns. - the value corresponding to the name from the outer query is somehow passed in as an Expr of type T_Param, in the quals field. How can I retrieve the value from the Param struct ? You shouldn't be trying to do it at that level --- at most you ought to do ExecEvalExpr on the expression tree. Otherwise you're going to end up reinventing most of execQual.c to cover all cases. Right now we don't have very much infrastructure for helping FDWs push restriction clauses over to the far end, which is what you seem to be trying to do. I hope that will emerge in 9.2 or 9.3. If you want to help you could start by looking at what the indexscan machinery does to extract usable indexquals, because that's more or less the same problem. If you feel that's out of your league, I'd suggest not bothering with pushing clauses across right now. The source does not help me much with what to do regarding the various fields in the struct. Does postgresql really fetch all columns in a subselect, or am I just parsing the tree in a wrong way ? Hmm, yeah, the planner thinks it's cheaper to extract all columns than just some of them. This is appropriate for plain tables but I can see that it's a bit dubious for a foreign table. Maybe we should shut off the use_physical_tlist optimization for foreign tables. However, it's not really intended that FDWs should be relying on the execution-time targetlist to figure out which columns to pull across anyway. The right way to make such an optimization is for the plan-time hook to determine which columns are needed (look at the reltargetlist) and save that information in the plan node. 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] inserting bytea using PHPs pg_escape_bytea()
El día 20 de octubre de 2011 14:28, Rodrigo Gonzalez rjgonz...@estrads.com.ar escribió: El 20/10/11 14:13, Martín Marqués escribió: El día 19 de octubre de 2011 23:20, Jeff Davis pg...@j-davis.com escribió: On Wed, 2011-10-19 at 14:30 -0300, Martín Marqués wrote: The only concern I have is that on insertion, I get this WARNING: WARNING: nonstandard use of \\ in a string literal at character 41 HINT: Use the escape string syntax for backslashes, e.g., E'\\'. Should I worry? What does it mean? First of all, the best solution is to use parameterized queries: http://us.php.net/manual/en/function.pg-query-params.php How would that work with abstraction layers like MDB2 or PDO? With PDO just check http://www.php.net/manual/en/pdo.prepare.php AFAIK, that's not the same as pg_query_params. Thats more like pg_prepare() + pg_execute(). I use it a lot with MDB2, but couldn't find something like pg_query_params() -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] inserting bytea using PHPs pg_escape_bytea()
On Thu, 2011-10-20 at 14:13 -0300, Martín Marqués wrote: How would that work with abstraction layers like MDB2 or PDO? I'm not sure. If there isn't some way to use parameterized queries, then it's not a very good abstraction layer, in my opinion (because parameterized queries are widely recognized as a good idea). Sometimes it is tied to the mechanism for preparing a query -- you might try that. The only place I get these messages are when inserting (or updateing) bytea columns with images (normally jpeg and png). That's probably because normal strings aren't as likely to use escape sequences. But binary data pretty much needs to, so it does octal escapes (or is it hex now?), like: \000 for a zero byte. However, because the non-standard string literals allow for backslash escapes as well, it ends up looking like (for standard_conforming_strings=FALSE): '\\000' after escaping the bytea and escaping it to be a string literal. When standard_conforming_strings is on, then backslash is no longer a special character in string literals, so it can just do the bytea escaping and that's it, so the zero byte as a string literal would look like: '\000' or perhaps: '\x00' I hope this helps. My advice is to just try it in different ways and see what strings are sent to postgresql (by setting log_statement_min_duration=0, which will log all the SQL). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovery from Archive files
On 10/20/11 2:33 AM, Raghavendra wrote: Am assuming you are having $PGDATA (data directory) and their WAL Archives. he said he does not have the base backup ($PGDATA), so this is pointless. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extraneous Files
On Thu, Oct 20, 2011 at 1:12 PM, Ian Harding harding@gmail.com wrote: If someone happened to accidentally end up with a lot of files that were NOT part of their database in the data/base/X directory, how could they go about getting a reliable list of files they could safely delete? The files were there before the current incarnation of the database, so have ctime before the good ones, but may be valid because they were updated by rsync, but I don't know that mtime is reliable for every file either, since some may have been unchanged from the first time this someone ran rsync. Not a super big deal, just wasting a lot of space... It is a big deal, one should NEVER EVER manually delete files from under the data directory. If you want to remove the data, you can figure out what relations the files tie back to by examining 'relfilenode' on pg_class... select relname from pg_class where relfilenode='X' -- is the name of the file before a period If you want to delete the files, you can then 'drop table relname' . If you value the logical consistency of your database, I wouldn't delete anything unless you go through the database engine with a 'drop' command. --Scott Thanks! Ian postgresql 9.0.4 on linux -- 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] Recovery from Archive files
On Thu, Oct 20, 2011 at 11:58 PM, John R Pierce pie...@hogranch.com wrote: On 10/20/11 2:33 AM, Raghavendra wrote: Am assuming you are having $PGDATA (data directory) and their WAL Archives. he said he does not have the base backup ($PGDATA), so this is pointless. Ahh.. Agreed. I mis-read, I though he is trying to recovery from current DATA directory and not with base backup which he doesn't have it. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] a set of key/value pairs
Does anyone have a specific example of how to: 1) create a variable that is a set or array of key value pairs? 2) how to populate each element of the array (each element being a key / value pair) 3) how to iterate through the list or array retrieving each key/value pair? Please do not point me to this doc: http://developer.postgresql.org/pgdocs/postgres/hstore.html What I am looking for is an example as listed in 1..3 above, and that is not provided in the doc. Regards, J.V. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql at what point does the knowledge of the query come in?
I am struggling to understand at what point the query knowledge comes into
play here.
Ideally it should look in nmarker and if there is an 'N' then execute the
query (but how would it know that without running the query first?) and
return the results in the nnodetest, but (in its current form it seems to be
finding the first match (with the if statement commented out) then leaving
the others blank.
here is the function followed by the query followed by the output:
Function
--
--current identified issues, 1)the function seems to stop after the first
match, 2)can't get it to only look at the ones with nmarker of 'N'
create or replace function nnodetest(text) returns text language plpgsql as
$$
DECLARE
newnode alias for $1;
nnlength integer;
t text;
nmarker text;
BEGIN
nnlength := length(newnode);
for i in 1..(nnlength-1) loop
--if nmarker = 'N' then
select into t
node
from
(Values('one',''),('tw',''),('threee',''),('four',''),('five',''),('eights','N'),('seven',''),('eight',''),('three',''),('two','N'))
blast(node,nmarker)
where node = substring(newnode,1,i-1)||substring(newnode,i+1,nnlength);
--end if;
end loop;
return t;
END;
$$
Query
--
select
node,
nmarker,
nnodetest(node)
from
(Values('one',''),('tw',''),('threee',''),('four',''),('five',''),('eights','N'),('seven',''),('eight',''),('three',''),('two','N'))
blast(node,nmarker)
Output
--
one;;
tw;;
threee;;three
four;;
five;;
eights;N;
seven;;
eight;;
three;;
two;N;
whereas the output should be:
one;;
tw;;
threee;;
four;;
five;;
eights;N;eight
seven;;
eight;;
three;;
two;N;two
Re: [GENERAL] Extraneous Files
On Thu, Oct 20, 2011 at 02:32:18PM -0400, Scott Mead wrote: On Thu, Oct 20, 2011 at 1:12 PM, Ian Harding harding@gmail.com wrote: If someone happened to accidentally end up with a lot of files that were NOT part of their database in the data/base/X directory, how could they go about getting a reliable list of files they could safely delete? The files were there before the current incarnation of the database, so have ctime before the good ones, but may be valid because they were updated by rsync, but I don't know that mtime is reliable for every file either, since some may have been unchanged from the first time this someone ran rsync. Not a super big deal, just wasting a lot of space... It is a big deal, one should NEVER EVER manually delete files from under the data directory. If you want to remove the data, Well, Ian isn't talking about removing data. What he was asking (I believe) is how to remove from the data directory files which got nothing to do with the database in question (but probably look like database files because, say, someone copied *another* database into the directory ;-) Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] a set of key/value pairs
On 10/20/11 11:53 AM, J.V. wrote: Does anyone have a specific example of how to: 1) create a variable that is a set or array of key value pairs? 2) how to populate each element of the array (each element being a key / value pair) 3) how to iterate through the list or array retrieving each key/value pair? in what programming language? in SQL, there aren't 'variables', there are relations, and you don't 'iterate', you query. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cleaning up index names
I have a database which was created with DDL from several different tools. Each one seems to have had their own naming convention for indexes. I'd like to rename them to be consistent. Has anyone seen a tool or put together a script to do this based on the catalog data? __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com/ * mike.blackw...@rrd.com*
[GENERAL] Reading PG data from MySQL stored procedure
Here's a real long shot, but what the heck... I have a user that's using a system that gives him a single MySQL DB handle to work with. He must get all his data through this handle. He wants some of my PG based data. Not only can't he open a new DB handle to my PG DB, but he cannot even run something at the system level (otherwise I'd just give him a psql -P pager=off db -c select foo from bar ). So it has to come through MySQL somehow, MySQL V5.1 no less. It appears that they have some sort of external language capability for stored procedures in their V6. And they have something else which'll run an external object (UDF something or other). But what I'm throwing out there is a question regarding any known data interchange functionality that might exist between these 2 different SQL DB engines. As I said, a real long shot. Thanks for any comments (even if it's NO :-) )
Re: [GENERAL] Extraneous Files
On 10/20/11 12:14 PM, Karsten Hilbert wrote: Well, Ian isn't talking about removing data. What he was asking (I believe) is how to remove from the data directory files which got nothing to do with the database in question (but probably look like database files because, say, someone copied*another* database into the directory ;-) pg_dumpall stop postgres save the .conf files wipe $PGDATA initdb restore the .conf files restart postgres restore the dumpall -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extraneous Files
Well, they are actually streaming replication slaves, and I boogered up the rsync command, so there they are. I diffed the directories from the master to the slave, and think I will go ahead and delete all the files that don't appear in both places and see what happens. Worst case, I have to set them back up again. No big deal... Thanks! - Ian On Thu, Oct 20, 2011 at 12:24 PM, John R Pierce pie...@hogranch.com wrote: On 10/20/11 12:14 PM, Karsten Hilbert wrote: Well, Ian isn't talking about removing data. What he was asking (I believe) is how to remove from the data directory files which got nothing to do with the database in question (but probably look like database files because, say, someone copied*another* database into the directory ;-) pg_dumpall stop postgres save the .conf files wipe $PGDATA initdb restore the .conf files restart postgres restore the dumpall -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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 PG data from MySQL stored procedure
On Thu, Oct 20, 2011 at 2:22 PM, Gauthier, Dave dave.gauth...@intel.com wrote: Here's a real long shot, but what the heck... I have a user that's using a system that gives him a single MySQL DB handle to work with. He must get all his data through this handle. He wants some of my PG based data. Not only can't he open a new DB handle to my PG DB, but he cannot even run something at the system level (otherwise I'd just give him a psql -P pager=off db -c select foo from bar ). So it has to come through MySQL somehow, MySQL V5.1 no less. It appears that they have some sort of external language capability for stored procedures in their V6. And they have something else which'll run an external object (UDF something or other). But what I'm throwing out there is a question regarding any known data interchange functionality that might exist between these 2 different SQL DB engines. First question: why in the world can't you do the processing on the client side? There are gajillion and one data transfer/ETL tools! mysql of that has pretty much zero server side extensiblity AFAIK. Of course, it would be quite possible from postgres to query mysql database, but I digress... 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] Reading PG data from MySQL stored procedure
The software system they are being forced to use gives them the ability to send queries to a MySQL which has already been connected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn't coding anything per-se, they're just using the interface provided. But they can call a stored procedure/function because that's ligit sql. The data that flows from that goes into other parts of the system for reporting, etc... . -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Thursday, October 20, 2011 3:33 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Reading PG data from MySQL stored procedure On Thu, Oct 20, 2011 at 2:22 PM, Gauthier, Dave dave.gauth...@intel.com wrote: Here's a real long shot, but what the heck... I have a user that's using a system that gives him a single MySQL DB handle to work with. He must get all his data through this handle. He wants some of my PG based data. Not only can't he open a new DB handle to my PG DB, but he cannot even run something at the system level (otherwise I'd just give him a psql -P pager=off db -c select foo from bar ). So it has to come through MySQL somehow, MySQL V5.1 no less. It appears that they have some sort of external language capability for stored procedures in their V6. And they have something else which'll run an external object (UDF something or other). But what I'm throwing out there is a question regarding any known data interchange functionality that might exist between these 2 different SQL DB engines. First question: why in the world can't you do the processing on the client side? There are gajillion and one data transfer/ETL tools! mysql of that has pretty much zero server side extensiblity AFAIK. Of course, it would be quite possible from postgres to query mysql database, but I digress... 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] a set of key/value pairs
On Thu, Oct 20, 2011 at 1:53 PM, J.V. jvsr...@gmail.com wrote:
Does anyone have a specific example of how to:
1) create a variable that is a set or array of key value pairs?
2) how to populate each element of the array (each element being a key /
value pair)
3) how to iterate through the list or array retrieving each key/value
pair?
create type pair_t as (key text, value text);
select array[('a', '123'), ('b', '234')]::pair_t[];
select (unnest(array[('a', '123'), ('b', '234')]:: pair_t[])).*;
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] plpgsql at what point does the knowledge of the query come in?
I found the problem, it looks like nnlength := length(newnode); is not
getting reset
create or replace function nnodetestt(text) returns text language plpgsql as
$$
DECLARE
newnode alias for $1;
nnlength integer;
t text;
nmarker text;
BEGIN
nnlength := length(newnode);
for i in 1..(nnlength-1) loop
select into t
node
from
(Values('thr','N'),('threee',''),('fiveu','N'),('five',''))
blast(node,nmarker)
where node = substring(newnode,1,i-1)||substring(newnode,i+1,nnlength);
end loop;
return t;
END;
$$
select
node as node_orig,
nmarker,
nnodetestt(node),
case
when nmarker = 'N' then
nnodetestt(node)
end
from
(Values('thr','N'),('threee',''),('fiveu','N'),('five',''))
blast(node,nmarker)
thr;N;threee;threee
threee;;;
fiveu;N;;
five;;;
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On 20/10/2011 21:51, Henry Drexler wrote: I found the problem, it looks like nnlength := length(newnode); is not getting reset create or replace function nnodetestt(text) returns text language plpgsql as $$ DECLARE newnode alias for $1; nnlength integer; t text; nmarker text; BEGIN nnlength := length(newnode); Not sure what you mean by the above... that statement only gets executed once, so the value of nnlength doesn't change. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 disable all pkey/fkey constraints globally
Is there a simpler way than this to query the database for meta-data and get the constraint definitions? If I have the constraint name (which I do), I could store the constraint definition to a file or database table and recreate them if I could get the definition. This seems like a very simple thing to do, but nowhere can I find the meta-data I would need to first save the constraint, to later re-create it. thanks On 10/5/2011 3:27 AM, Joe Abbate wrote: On 10/05/2011 04:49 AM, depst...@alliedtesting.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of J.V. Sent: Tuesday, October 04, 2011 10:00 PM To: pgsql-general Subject: [GENERAL] how to disable all pkey/fkey constraints globally Is there a generic way to drop just all primary key and foreign key constraints on a given table? I know how to do given the specific name of the constraint. same question but one statement that would just disable all primary key and foreign key constraints on a given database? and am assuming the reverse could not be done because would have to re- create each one individually? Maybe I do not want to drop, so is there a way to simply disable all globally (not drop) then enable all globally? You can find all foreign key constraints for a given table, save constraint definitions, drop constraints, and later re-enable them. Look into table pg_constraint and function pg_get_constraintdef. If you'll allow me to toot my horn, here's an alternative: - Use dbtoyaml [1] to output your tables to a file, say, yaml1 - Edit the yaml1 file, searching for primary_key and foreign_keys and remove those you want to drop, save the result to a different file, say, yaml2 - Use yamltodb [2] with yaml2 to generate SQL to drop the primary keys and foreign keys, in the correct dependency order (at least that's what it's supposed to do, make sure you use the -1 option), to a file, say, sql1 - Run sql1 through psql to drop the constraints - Use yamltodb with yaml1 to generate SQL to recreate the primary keys and foreign keys to, say sql2 - Run sql2 through psql to recreate the constraints Regards, Joe [1] http://www.pyrseas.org/docs/dbtoyaml.html [2] http://www.pyrseas.org/docs/yamltodb.html -- 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] plpgsql at what point does the knowledge of the query come in?
On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell r...@iol.ie wrote: Not sure what you mean by the above... that statement only gets executed once, so the value of nnlength doesn't change. Ray. doesn't the function get executed once for each row in the query? so in the below example thr will match threee but fiveu will not match five because it has not reset nnlength := length(newnode); to measure the length of fiveu but is retaining the length of thr is it possible to get nnlength := length(newnode); to reset for each new row/node in the query?
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell r...@iol.ie wrote: Not sure what you mean by the above... Ray. This is what I thought it was doing. 1. it gets the node from the first row 2. measures its length 3. then loops through removing one character at a time and comparing that to the whole column/query 4. for thr it found a match in threee then the function starts over for the next row 1. it gets the node from the second row 2. measures the length of the string 3. then loopse 4. ... so it is the step 2 that looks like is not getting reset with each new nnodetestt(node) in the select statement when it calls the function.
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On 20/10/2011 22:31, Henry Drexler wrote: On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell r...@iol.ie mailto:r...@iol.ie wrote: Not sure what you mean by the above... Ray. This is what I thought it was doing. 1. it gets the node from the first row 2. measures its length 3. then loops through removing one character at a time and comparing that to the whole column/query 4. for thr it found a match in threee I was just trying to figure your function out... :-) I think you're mistaken about step 3 - This statement - node = substring(newnode, 1, i-1) || substring (newnode, i+1, nnlength) - is contatenating two substrings - the first bit (up to the i-th character) and the rest, and then comparing that to node. In fact, the second substring() call looks as if it will overrun the end of the string in newnode. What version of PostgreSQL are you using? The docs for 9.0 show two substring functions: substring(string [from int] [for int]) substr(string, from [, count]) and a couple of variants using regexps, and what you have above doesn't match any of them. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 disable all pkey/fkey constraints globally
On 10/20/2011 05:10 PM, J.V. wrote: Is there a simpler way than this to query the database for meta-data and get the constraint definitions? If I have the constraint name (which I do), I could store the constraint definition to a file or database table and recreate them if I could get the definition. This seems like a very simple thing to do, but nowhere can I find the meta-data I would need to first save the constraint, to later re-create it. The metadata is all in the PostgreSQL catalogs. One could write PL/pgSQL (or other PL) functions to select all the info from the catalogs into a simple text (SQL statement) format (e.g., ALTER TABLE tbl ADD CONSTRAINT ...), and also to generate or execute the disabling (ALTER TABLE tbl DROP CONSTRAINT ...) statements. You may think it's very simple, but I can assure you that adding or dropping constraints is not so easy because the ADDs and DROPs have to be issued in an order that takes into account inter-table/constraint dependencies (including, in the general case, constraints that are inherited between tables). Joe -- 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] plpgsql at what point does the knowledge of the query come in?
On 20/10/2011 22:13, Henry Drexler wrote: On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell r...@iol.ie mailto:r...@iol.ie wrote: Not sure what you mean by the above... that statement only gets executed once, so the value of nnlength doesn't change. Ray. doesn't the function get executed once for each row in the query? so in the below example Ah, OK - my misunderstanding - I thought you meant within one execution of the function. thr will match threee but fiveu will not match five because it has not reset nnlength := length(newnode); to measure the length of fiveu but is retaining the length of thr Are you sure about this? Try using RAISE NOTICE statements in the function to output the value of nnlength each time it's executed. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Date Range Using Months and Days Only
Thanks for the reply and assistance. I share your concern that the approach may be slow. I am not adverse to creating some sort of table to store the time periods with years for the temporal extent of the data if that would speed up the process. In that situation there would be multiple records (one record for each year) that it would need to check against. I suppose I could then create some sort of aggregate to then count the number of records in which the date falls within the range. Anything over 0 would indicate that the date does fall within the range? From: Adam Cornett [mailto:adam.corn...@gmail.com] Sent: Wednesday, October 19, 2011 6:51 PM To: Jeff Adams Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Date Range Using Months and Days Only On Wed, Oct 19, 2011 at 12:40 PM, Jeff Adams jeff.ad...@noaa.gov wrote: Greetings, I have to write a query on a fairly large table of data (100 million rows) where I need to check to see if a date (epoch) falls between a range of values. The catch is that the range is defined only by month and day values. For example the record containing the epoch value will be linked to a table that containing columns named start_month, start_day, end_month, end_day that define the range. With respect to the range, year does not matter, however, some of the ranges will start in November and end in April of the next year. Has anyone come across this type of query? I could certainly write a function or even include criteria in a query that would extract date parts of the epoch and then compare against the values in the start_month, start_day, end_month, end_day (it might get complex with respect to ranges where the start year and end year are different), but I am worried about performance. I thought I would seek some input before I floundered through the many iterations of poor performing alternatives! Any thoughts would be greatly appreciated. Thanks in advance... Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general create table a ( id_a integer, epoch integer ); create table b ( id_b integer, start_month integer, start_day integer, end_month integer, end_day integer ); CREATE OR REPLACE FUNCTION get_timestamps(sm integer, sd integer, em integer, ed integer, year integer) RETURNS timestamp without time zone[] AS $BODY$ declare syear integer := year; eyear integer := year; tstamps timestamp[]; begin if(smem) then -- assume that since the end month is less than the start month is in the next year eyear := eyear+1; end if; tstamps[0] := (syear|| '-' || sm || '-' || sd)::timestamp; tstamps[1] := (eyear|| '-' || em || '-' || ed)::timestamp; return tstamps; end $BODY$ LANGUAGE plpgsql STABLE; create view a_timestamp as SELECT id_a, TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second' as tstamp from a; with ab as( select id_a,id_b,get_timestamps(b.start_month,b.start_day,b.end_month,b.end_day, extract(year from a.tstamp)::integer) ts_arr,a.tstamp from a_timestamp as a,b ) select * from ab where ab.tstamp between ts_arr[0] and ts_arr[1] This obviously isn't a fast solution to your problem, although converting the integer epoch to a timestamp in table a would eliminate the view a_timestamp and you can index the column for some speed up, the real problem you're facing is that your ranges don't have years, otherwise you could store everything as a timestamp and then just join using 'between' and postgres would just need to do an index scan on each table. -Adam -- 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] plpgsql at what point does the knowledge of the query come in?
On 20/10/2011 22:42, Raymond O'Donnell wrote: - is contatenating two substrings - the first bit (up to the i-th Whoops - concatenating :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] plpgsql at what point does the knowledge of the query come in?
On Thu, Oct 20, 2011 at 5:42 PM, Raymond O'Donnell r...@iol.ie wrote:
I was just trying to figure your function out... :-) I think you're
mistaken about step 3 - This statement -
node = substring(newnode, 1, i-1) || substring (newnode, i+1, nnlength)
- is contatenating two substrings - the first bit (up to the i-th
character) and the rest, and then comparing that to node.
In fact, the second substring() call looks as if it will overrun the end
of the string in newnode.
yes is it grouping both together, see here for a visual, it is part of what
I used to build it:
select
node,
substring(node,1,1-1)||substring(node,1+1,character_length(node)),
substring(node,1,2-1)||substring(node,2+1,character_length(node)),
substring(node,1,3-1)||substring(node,3+1,character_length(node)),
substring(node,1,4-1)||substring(node,4+1,character_length(node)),
substring(node,1,5-1)||substring(node,5+1,character_length(node)),
substring(node,1,6-1)||substring(node,6+1,character_length(node)),
substring(node,1,7-1)||substring(node,7+1,character_length(node))
from
(Values('thr','N'),('threee',''),('fiveu','N'),('five',''))
blast(node,nmarker)
the steps out will constrain it properly in the original function.
What version of PostgreSQL are you using? The docs for 9.0 show two
substring functions:
substring(string [from int] [for int])
substr(string, from [, count])
and a couple of variants using regexps, and what you have above doesn't
match any of them.
Ray.
Using 9.1 I have always used that syntax as it worked, though yes, looking
at the docs the following also works (proper syntax):
where node = substring(newnode from 1 for i-1)||substring(newnode from i+1
for nnlength);
Thank you for the tip on the 'RAISE NOTICE statements' I have not come
across that before - that should really help in my trouble shooting.
[GENERAL] force JDBC driver fetch / autocommit parameters?
Greetings, I'm using the postgresql-9.1-901.jdbc4 driver in an application (that I don't control) that's not passing along my specified fetch and autocommit parameters. Is there anyway I can force the driver to use my required parameters outside of the application? Thanks, Sean
Re: [GENERAL] Date Range Using Months and Days Only
This seems like it will be much faster, though I will most likely create epoch ranges and forgo creating timestamps for all of my epoch values in my table of 100+ million rows. Thanks again Adam! Jeff From: Adam Cornett [mailto:adam.corn...@gmail.com] Sent: Thursday, October 20, 2011 11:37 AM To: Jeff Adams Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Date Range Using Months and Days Only On Thu, Oct 20, 2011 at 8:42 AM, Jeff Adams jeff.ad...@noaa.gov wrote: Thanks for the reply and assistance. I share your concern that the approach may be slow. I am not adverse to creating some sort of table to store the time periods with years for the temporal extent of the data if that would speed up the process. In that situation there would be multiple records (one record for each year) that it would need to check against. I suppose I could then create some sort of aggregate to then count the number of records in which the date falls within the range. Anything over 0 would indicate that the date does fall within the range? From: Adam Cornett [mailto:adam.corn...@gmail.com] Sent: Wednesday, October 19, 2011 6:51 PM To: Jeff Adams Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Date Range Using Months and Days Only On Wed, Oct 19, 2011 at 12:40 PM, Jeff Adams jeff.ad...@noaa.gov wrote: Greetings, I have to write a query on a fairly large table of data (100 million rows) where I need to check to see if a date (epoch) falls between a range of values. The catch is that the range is defined only by month and day values. For example the record containing the epoch value will be linked to a table that containing columns named start_month, start_day, end_month, end_day that define the range. With respect to the range, year does not matter, however, some of the ranges will start in November and end in April of the next year. Has anyone come across this type of query? I could certainly write a function or even include criteria in a query that would extract date parts of the epoch and then compare against the values in the start_month, start_day, end_month, end_day (it might get complex with respect to ranges where the start year and end year are different), but I am worried about performance. I thought I would seek some input before I floundered through the many iterations of poor performing alternatives! Any thoughts would be greatly appreciated. Thanks in advance... Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general create table a ( id_a integer, epoch integer ); create table b ( id_b integer, start_month integer, start_day integer, end_month integer, end_day integer ); CREATE OR REPLACE FUNCTION get_timestamps(sm integer, sd integer, em integer, ed integer, year integer) RETURNS timestamp without time zone[] AS $BODY$ declare syear integer := year; eyear integer := year; tstamps timestamp[]; begin if(smem) then -- assume that since the end month is less than the start month is in the next year eyear := eyear+1; end if; tstamps[0] := (syear|| '-' || sm || '-' || sd)::timestamp; tstamps[1] := (eyear|| '-' || em || '-' || ed)::timestamp; return tstamps; end $BODY$ LANGUAGE plpgsql STABLE; create view a_timestamp as SELECT id_a, TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second' as tstamp from a; with ab as( select id_a,id_b,get_timestamps(b.start_month,b.start_day,b.end_month,b.end_day, extract(year from a.tstamp)::integer) ts_arr,a.tstamp from a_timestamp as a,b ) select * from ab where ab.tstamp between ts_arr[0] and ts_arr[1] This obviously isn't a fast solution to your problem, although converting the integer epoch to a timestamp in table a would eliminate the view a_timestamp and you can index the column for some speed up, the real problem you're facing is that your ranges don't have years, otherwise you could store everything as a timestamp and then just join using 'between' and postgres would just need to do an index scan on each table. -Adam Here is an updated method, it involves creating a third table to store the timestamp values from table 'b' to allow for better lookups, I've also added a timestamp column to table 'a' (which can be calculated from the epoch using the view in my previous email. create table a ( id_a integer, epoch integer, ts timestamp ); CREATE INDEX ON a (ts); create table b ( id_b integer, start_month integer, start_day integer, end_month integer, end_day integer ); CREATE OR REPLACE FUNCTION get_timestamps(sm integer, sd integer, em integer, ed integer, year integer) RETURNS timestamp without time zone[] AS $BODY$ declare syear integer := year; eyear integer := year; tstamps timestamp[]; begin if(smem) then -- assume that since the end month is less than the
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On Thu, Oct 20, 2011 at 5:41 PM, Raymond O'Donnell r...@iol.ie wrote: Are you sure about this? Try using RAISE NOTICE statements in the function to output the value of nnlength each time it's executed. Ray. Thank you for showing me the 'Rase Notice' , I had not seen that before and it helped me solve my problem right away. I appreciate the help you have given - thank you.
Re: [GENERAL] Reading PG data from MySQL stored procedure
On 10/21/2011 03:56 AM, Gauthier, Dave wrote: The software system they are being forced to use gives them the ability to send queries to a MySQL which has already been connected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn't coding anything per-se, they're just using the interface provided. But they can call a stored procedure/function because that's ligit sql. The data that flows from that goes into other parts of the system for reporting, etc... . AFAIK, the only way you'd be able to get from MySQL to Pg directly would be to install a user-defined function written in C that used libpq to connect to Pg. You can't do that over a basic connection to MySQL, you need the ability to install binaries on the server. The system is too locked down to permit what you want to do on the MySQL end. You'd have to make a connection to Pg from the client side, extract the data you wanted and send it down the connection handle for MySQL that you already have. There's a bit too much hand-waving and not enough specifics about language, environment, etc to say anything more. Is this some kind of report-writing system? A RAD environment? What? -- Craig Ringer -- 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 PG data from MySQL stored procedure
A big part of thisinquiry has to do with feasibility. Another option is to approach those who control that system which the user has to use and get them to be more accommodating with regard to attaching to more than one DB and realizing that there are other DB engines than MySQL. Armed with the info you guys have given me, I will propose to them that they ask their app provider the means to attach to multiple ODBC served DBs (this thing is a Windows based app). That way they can use MySQL and PG and whatever else they might have to deal with in the future. Many thanks for the input. As I said, it was a long shot. So I got what I expected. At least now I can tell them that I am not alone is my opinion that getting PG date througn MySQL is a bad idea. Thanks Again! -Original Message- From: Craig Ringer [mailto:ring...@ringerc.id.au] Sent: Thursday, October 20, 2011 10:23 PM To: Gauthier, Dave Cc: Merlin Moncure; pgsql-general@postgresql.org Subject: Re: [GENERAL] Reading PG data from MySQL stored procedure On 10/21/2011 03:56 AM, Gauthier, Dave wrote: The software system they are being forced to use gives them the ability to send queries to a MySQL which has already been connected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn't coding anything per-se, they're just using the interface provided. But they can call a stored procedure/function because that's ligit sql. The data that flows from that goes into other parts of the system for reporting, etc... . AFAIK, the only way you'd be able to get from MySQL to Pg directly would be to install a user-defined function written in C that used libpq to connect to Pg. You can't do that over a basic connection to MySQL, you need the ability to install binaries on the server. The system is too locked down to permit what you want to do on the MySQL end. You'd have to make a connection to Pg from the client side, extract the data you wanted and send it down the connection handle for MySQL that you already have. There's a bit too much hand-waving and not enough specifics about language, environment, etc to say anything more. Is this some kind of report-writing system? A RAD environment? What? -- Craig Ringer -- 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 PG data from MySQL stored procedure
On 10/21/2011 11:21 AM, Gauthier, Dave wrote: A big part of thisinquiry has to do with feasibility. Another option is to approach those who control that system which the user has to use and get them to be more accommodating with regard to attaching to more than one DB and realizing that there are other DB engines than MySQL. Armed with the info you guys have given me, I will propose to them that they ask their app provider the means to attach to multiple ODBC served DBs (this thing is a Windows based app). That way they can use MySQL and PG and whatever else they might have to deal with in the future. Many thanks for the input. As I said, it was a long shot. So I got what I expected. At least now I can tell them that I am not alone is my opinion that getting PG date througn MySQL is a bad idea. It's not necessarily a bad idea, it's just not going to be _possible_ with the constraints on their use of MySQL and their limited access to the system. It's really handy to be able to fetch data from one DB via another; that's what dblink and dbilink in PostgreSQL are for. MySQL has various foreign data engines for the same purpose. If the DBs of interest are co-located it makes a LOT more sense to get one to pull data from the other rather than sending it from DBa to a remote client that then sends it back to DBb. You just can't do that, because you don't seem to have any kind of useful access to the system the DB runs on. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
On Wed, Oct 5, 2011 at 1:24 AM, Dave Page dp...@pgadmin.org wrote: On Wednesday, October 5, 2011, Thomas Kellerer spam_ea...@gmx.net wrote: Dave Page, 04.10.2011 21:46: We updated our build system to use BitRock 7 today (for unrelated reasons) which has new features for ACL management. We're going to investigate replacing cacls/icacls with those features tomorrow and will create some test builds ASAP. If you can provide the test builds publicly, I will be happy to test them and see if that behaves differently on my system. Thanks, we will. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company As someone who recently spent a couple of days fighting with icacls, I thought I might offer some insight here. What I discovered through trial and error and much googling is that icacls has some non-intuitive behaviors which are not at all obvious from just reading the documentation. For example, it behaved entirely differently if you run it against a directory instead of a file (which may contain wildcards). The command icacls.exe C:\mydir\ options (when we targeted a directory) applied options to every file in C:\mydir\ and all subdirectories. icacls.exe C:\mydir\* options (when we targeted a file) applied options to all files in C:\, but did not apply options to files in subdirectories unless the /t switch was provided. This behavior is not directly mentioned in the documentation, but can be inferred from the first 2 examples, if you look at them carefully. Also, in the syntax description, the /t switch is shown for the icacls.exe FileName syntax, but not for the icacls.exe Directory syntax. I never would have noticed these if I weren't looking specifically for an explanation of the observed behavior. As far as how to use icacls to set permisions on a directory (as opposed to the files in a directory) without recursing to all subdirectories, I never did succeed in finding that out.
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
On 10/21/2011 01:43 PM, Eric McKeeth wrote: As far as how to use icacls to set permisions on a directory (as opposed to the files in a directory) without recursing to all subdirectories, I never did succeed in finding that out. Could it depend on the presence or absence of a trailing slash? Some interesting commands (I'm looking at you, rsync) are guilty of changing behavior based on that. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
