Re: [GENERAL] Performance tuning in Pgsql
On Fri, Dec 10, 2010 at 12:53 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I am researched a lot about Performance tuning in Pgsql. I found that we have to change shared_buffer parameter and effective_cache_size parameter. I changed shared_buffer to 2 GB but I can't able to locate effective_cache_size parameter in postgresql.conf file. Odd, it's there in mine. So, what OS are you using, what pg version, etc. First place to look for performance tuning is the pg wiki entry on just that: http://wiki.postgresql.org/wiki/Performance_Optimization Also i want to change my WAL directory to seperate directory. Same I couldn,t locate pg_xlog or how to change it. OK, so the way I do this, is to locate my data directory. On a stock Ubuntu machine that would be /var/lib/postgresql/8.4/main . In that dir is a directory called pg_xlog, what we're looking for. So, as root, I'd do: cd /var/lib/postgresql/8.4/main /etc/init.d/postgresql-8.4 stop mkdir /myothervolume/pg_xlog chown postgres.postgres /myothervolume/pg_xlog chmod 700 /myothervolume/pg_xlog cp -rf pg_xlog/* /myothervolume/pg_xlog mv pg_xlog pg_xlog_old ln -s /myothervolume/pg_xlog pg_xlog /etc/init.d/postgresql-8.4 start -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A cronjob for copying a table from Oracle
Hello, I'd like to have a local PostgreSQL copy of a table stored (and growing) at the remote Oracle database: SQL desc qtrack; Name Null?Type - ID NOT NULL VARCHAR2(20) EMAIL VARCHAR2(320) OSVERSION VARCHAR2(30) APPSVERSION VARCHAR2(30) QDATETIME DATE CATEGORY VARCHAR2(120) BETA_PROG VARCHAR2(20) CATINFO VARCHAR2(120) DEVINFO VARCHAR2(4000) NAME VARCHAR2(20) FORMFACTOR VARCHAR2(10) DETAILS VARCHAR2(50) EMAILID VARCHAR2(16) SQL select id, qdatetime, dump(qdatetime) from qtrack where qdatetime sysdate - 1 order by qdatetime ID QDATETIME - DUMP(QDATETIME) 2002121116281369127-SEP-34 Typ=12 Len=7: 142,134,9,27,22,20,13 2002121202429070 28-SEP-34 Typ=12 Len=7: 142,134,9,28,8,34,20 2002121205252047228-SEP-34 Typ=12 Len=7: 142,134,9,28,8,60,32 2002121310073187 28-SEP-34 Typ=12 Len=7: 142,134,9,28,16,20,48 .. 10106 rows selected. The ID is a string 20101210_some_random_numbers (not growing :-( and they should have made it a primary key probably?) I'm preparing a PHP-script to be run as a nightly cronjob and will first find the latest qdatetime stored in my local PostgreSQL database and then just select in remote Oracle, insert into the local PostgreSQL database in a loop. But I wonder if there is maybe a cleverer way to do this? And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Regards Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cannot Achieve Performance
Dear all, Performance tuning is what, which all i sured to achieve in pgsql. I am currently testing on 5 GB table with select operation that takes about 229477 ms ( 3.82 minutes ) with simple configuration. I have 4 GB RAM. So I change some parameters such as shared_buffers to 512 MB , effective cache size to 1024 MB and my separate log directory. Would this is sufficient or I need something else to be changed. But my output after changing these parameters is not what I am expecting of , it is 229971 ms ( 3.81 minutes ). Please help. Waiting for some pointers until I am googling. Thanks Regards Adarsh Sharma -- 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] Cannot Achieve Performance
On 10 December 2010 12:28, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, Performance tuning is what, which all i sured to achieve in pgsql. I am currently testing on 5 GB table with select operation that takes about 229477 ms ( 3.82 minutes ) with simple configuration. I have 4 GB RAM. So I change some parameters such as shared_buffers to 512 MB , effective cache size to 1024 MB and my separate log directory. Would this is sufficient or I need something else to be changed. But my output after changing these parameters is not what I am expecting of , it is 229971 ms ( 3.81 minutes ). Please help. Waiting for some pointers until I am googling. Thanks Regards Adarsh Sharma show us explain and explain analyze of this query, maybe the problem is not in the memory settings regards Szymon
Re: [GENERAL] A cronjob for copying a table from Oracle
Alexander Farber, 10.12.2010 12:02: I'm preparing a PHP-script to be run as a nightly cronjob and will first find the latest qdatetime stored in my local PostgreSQL database and then just select in remote Oracle, insert into the local PostgreSQL database in a loop. But I wonder if there is maybe a cleverer way to do this? And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Oracle's DATE includes a time part as well. So simply use a timestamp in PostgreSQL and everything should be fine. Regards Thomas -- 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 cronjob for copying a table from Oracle
On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer spam_ea...@gmx.net wrote: And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Oracle's DATE includes a time part as well. So simply use a timestamp in PostgreSQL and everything should be fine. Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp? (I realize that this more an Oracle question, sorry) What format string should I take for Oracle's to_date() function, I don't see a format string to get epoch seconds there Regards Alex -- 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 cronjob for copying a table from Oracle
Alexander Farber, 10.12.2010 12:53: On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellererspam_ea...@gmx.net wrote: And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Oracle's DATE includes a time part as well. So simply use a timestamp in PostgreSQL and everything should be fine. Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp? (I realize that this more an Oracle question, sorry) What format string should I take for Oracle's to_date() function, I don't see a format string to get epoch seconds there I have no idea what you are doing in PHP, but why don't you simply generate a valid date/time literal for Postgres using the to_char() function? Something like SELECT 'TIMESTAMP '''||to_char(QDATETIME, '-MM-DD HH24:MI:SS')|| FROM qtrack; That literal can directly be used in an INSERT statement for PostgreSQL Regards Thomas -- 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 cronjob for copying a table from Oracle
Hey Thomas, Alexander 2010/12/10 Thomas Kellerer spam_ea...@gmx.net Alexander Farber, 10.12.2010 12:53: On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellererspam_ea...@gmx.net wrote: And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Oracle's DATE includes a time part as well. So simply use a timestamp in PostgreSQL and everything should be fine. Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp? (I realize that this more an Oracle question, sorry) What format string should I take for Oracle's to_date() function, I don't see a format string to get epoch seconds there I have no idea what you are doing in PHP, but why don't you simply generate a valid date/time literal for Postgres using the to_char() function? Something like SELECT 'TIMESTAMP '''||to_char(QDATETIME, '-MM-DD HH24:MI:SS')|| FROM qtrack; That literal can directly be used in an INSERT statement for PostgreSQL He asked exactly that. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] Tuning Postgres for single user manipulating large amounts of data
On Thu, 2010-12-09 at 17:40 +, Paul Taylor wrote: what have I got to be careful of. I think that was in reference to turning fsync off, not work_mem values.
Re: [GENERAL] Tuning Postgres for single user manipulating large amounts of data
Hi ( sorry for the double posting, thought Id use the wrong email address but both have been posted anyway). As far as the db is concerned Im just reading data then writing the data to a lucene search index (which is outside of the database) , but my labtop is jut a test machine I want to run the same code on production. Why would work_mem not be safe at 64MB if I have 2GB of memory - what have I got to be careful of. Paul Well, the problem with work_mem is that it's 'per operation' so a query may actually need several work_mem segments. And it's not just sorting, a query containing a hash join, hash aggregate and a sort may consume up to 3x work_mem memory. And if you have a lot of concurrent users running such queries, you may easily run out of memory - in that case the feared OOM killer comes and kills one of the processes (usually postmaster, which means the database goes bottoms up). Not sure how OOM works on MacOS. But as you said there will be single user running queries on the database, you can set the work_mem limit pretty high. Depends on the queries though - a complicated query may consume a lot of memory. Tomas -- 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] Performance tuning in Pgsql
Please keep the list cc'd as there are others who might be able to help or could use this thread for help. On Fri, Dec 10, 2010 at 2:53 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Scott Marlowe wrote: On Fri, Dec 10, 2010 at 12:53 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I am researched a lot about Performance tuning in Pgsql. I found that we have to change shared_buffer parameter and effective_cache_size parameter. I changed shared_buffer to 2 GB but I can't able to locate effective_cache_size parameter in postgresql.conf file. Odd, it's there in mine. So, what OS are you using, what pg version, etc. First place to look for performance tuning is the pg wiki entry on just that: http://wiki.postgresql.org/wiki/Performance_Optimization Also i want to change my WAL directory to seperate directory. Same I couldn,t locate pg_xlog or how to change it. OK, so the way I do this, is to locate my data directory. On a stock Ubuntu machine that would be /var/lib/postgresql/8.4/main . In that dir is a directory called pg_xlog, what we're looking for. So, as root, I'd do: cd /var/lib/postgresql/8.4/main /etc/init.d/postgresql-8.4 stop mkdir /myothervolume/pg_xlog chown postgres.postgres /myothervolume/pg_xlog chmod 700 /myothervolume/pg_xlog cp -rf pg_xlog/* /myothervolume/pg_xlog mv pg_xlog pg_xlog_old ln -s /myothervolume/pg_xlog pg_xlog /etc/init.d/postgresql-8.4 start Thanks Scott , Very Nicely Explained. I followed your commands. I copied pg_xlog directory into /hdd-1/pg_xlog and chmod 700 to it. Also i make a link into /root/ directory. But when I start the server , I got the exception in startup.log which is FATAL: syntax error in file /hrd2-p/postgres_data/postgresql.conf line 245, near token / FATAL: syntax error in file /hrd2-p/postgres_data/postgresql.conf line 245, near token / FATAL: syntax error in file /hrd2-p/postgres_data/postgresql.conf line 247, near token / FATAL: syntax error in file /hrd2-p/postgres_data/postgresql.conf line 247, near token / FATAL: syntax error in file /hrd2-p/postgres_data/postgresql.conf line 247, near token / My postgresql.conf Line 247 is : #log_directory =/hrd2-1/pg_xlog # directory where log files are written, # can be absolute or relative to PGDATA I check it with # and without # but it doesn't work. But when I renamed pg_xlog_old to pg_xlog , Server starts. That doesn't make a lot of sense. The way I move pg_xlog doesn't involve that line really but kind of bypasses it. Got a complete example of all the steps you took? Does i need to change something in Postgresql.conf file? Possibly. It's one of the two ways of moving pg_xlog. More complete step by step example of what you tried will help. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Checking for data changes across a very large table
I'd like some general guidance on a security issue please. This may belong in the another list so please push me there if appropriate. We have an application design which includes a potential 2 billion row table (A). When the application user kicks off an analysis process, there is a requirement to perform a check on that table to verify that the data within hasn't changed. The initial intent was to hold a single checksum or equivalent in another small table (B). This checksum value would have been calculated over a subset of row values held within the 2bn row table. When the user chooses to kick off their processing, the application would re-calculate this checksum for table A in real time and compare the new result with the original value stored in table B. We might obtain the subset from table A like this: SELECT * FROM ( SELECT*, (row_number() OVER( ORDER BY ID))%1000 AS rn FROM TableA) aa WHERE aa.rn=1 LIMIT 2 ... and then perform an operation on column data from that subset (in this example, a 100,000th of the original data) to calculate a checksum value. Obviously the data within the subset would have to have been affected in order for a difference to be detected. It is a requirement that users have to have direct access to the DB (I hate this, but am overruled). I envisage that non-admin users would have read-only access, and the above validation would be an extra check on the data. But my main concern is performance - I fear that this won't be able to perform (in a few seconds rather than minutes). Does the general approach make any sense? Can anyone suggest a better starting point? This is Postgres 9.0. The minimum hardware spec is small and Windows-based (64-bit, Core i7 processor, 8GB RAM, single 1TB hard disk). My evaluation DB has ended up with the following config tweaks: shared_buffers 2048 MB work_mem 2047 MB checkpoint_segments 128 Thanks in advance. http://www.jbaconsulting.co.uk/ JBA Consulting South Barn Broughton Hall Skipton North Yorkshire BD23 3AE United Kingdom http://www.jbaconsulting.co.uk/?q=nce-winner-2010 t: +44 (0)1756 799919 | f: +44 (0)1756 799449 JBA is a Carbon Neutral Company. Please don't print this e-mail unless you really need to. This email is covered by JBA Consulting's http://www.jbaconsulting.co.uk/emaildisclaimer. image/gifimage/gif
Re: [GENERAL] Checking for data changes across a very large table
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 We have an application design which includes a potential 2 billion row table (A). When the application user kicks off an analysis process, there is a requirement to perform a check on that table to verify that the data within hasn't changed. ... But my main concern is performance - I fear that this won't be able to perform (in a few seconds rather than minutes). Does the general approach make any sense? Can anyone suggest a better starting point? It's not clear if you need to check the entire table, or just a subset related to that user each time, but one solution for either is a trigger (after insert, update, delete) that simply increments a sequence. Lock the sequence down and make the function security definer. Don't increment if an update hasn't actually changed anything. If the sequence number is not the same as last time the app checked, then the data is not the same. You can store the sequence value in a table if you need some persistence, or add multiple columns if you need to check for a user-derived subset of the data (with multiple sequences or simply increment the values in the table itself like a version control number). - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201012100942 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk0CPLYACgkQvJuQZxSWSsi8NQCgz4+bmWPMZm+aIX9maelZhj/+ wycAoNT32GFwudXF1Totvpw25+TXsu+E =jc8n -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schema manipulation from plpgsql
I was wondering if there are any schema manipulation statements which are not allowed from within a PL/PGSQL function. (Except from create/drop a database) -- 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] Schema manipulation from plpgsql
Le 10/12/2010 16:01, Gevik Babakhani a écrit : I was wondering if there are any schema manipulation statements which are not allowed from within a PL/PGSQL function. (Except from create/drop a database) create/drop tablespace They are the only exception AFAICT. -- Guillaume http://www.postgresql.fr http://dalibo.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] Quite a fast lockless vacuum full implemenation
On Thu, Dec 9, 2010 at 1:37 AM, Maxim Boguk maxim.bo...@gmail.com wrote: Hi there, First: I must say thanks to authors of this two posts: http://blog.endpoint.com/2010/09/reducing-bloat-without-locking.html and http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/ These two posts was provided me exellent ideas and starting point to create somewhat fast and reliable tool. Second: sorry for long post. I don't have my own blog to post such things. Unfortunatelly, exessive table bloat still can happen in real projects and task of compacting PostgreSQL table without long downtime is very common. So I wrote the tool which can perform incremental vacuum and compact table without completely locking target table. This message has 2 files attached: finished storable procedure and compact table wizard. They must be put in the same directory. Then run vacuum_table.pl --help to see possible options. Usage sample: ./vacuum_table.pl --dbname=billing --table=changes Storable procedure itself can be used stand-alone, but vacuum_table.pl is an easy to work with wizard to perform table compation. Before you choose to try it in production databases, PLEASE read source code and make sure you UNDERSTAND what is my code doing. Good features: 1) plays nice with triggers and rules on table (prevents on update trigger firing with set local session_replication_role to replica), therefore it can be used with active slony/londiste replication (on both master and slave servers). 2) has good performance (on my tests only 3-5 times slower than common VACUUM FULL) 3) can be restarted anytime 4) doesn't produce exessive index bloat (not like as VACUUM FULL) 5) is easy to use can you take some time to explain the mechanism of vacuum? looking at your code, the workhorse portion is the sql loop 'FOR _new_tid in EXECUTE...'. how does this compact the table/indexes? 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] monitoring warm standby lag in 8.4?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'm wondering if there's an accepted way to monitor a warm standby machine's lag in 8.4. The wiki[1] has a link[2] to a script which parses the output of pg_controldata, looking for a line like this: Time of latest checkpoint:Thu 09 Dec 2010 01:35:46 PM EST But I'm not sure whether this timestamp is to be trusted as an indicator of how far behind the standby is in its recovery -- this timestamp just tells us when the standby last performed a checkpoint, regardless of how far behind in the WAL stream it is, right? Correct. But since we cannot connect to a database in recovery mode, there are very few options to determine how far 'behind' it is. The pg_controldata is what the check_postgres program uses. This offers a rough check which is usually sufficient unless you have a very inactive database or need very fine grained checking. A better system would perhaps connect to both ends and examine which specific WALs were being shipped and which one was last played, but there are no tools I know of that do that. I suspect the reason for this is that the pg_controldata check is good enough. Certainly, that's what we are using for many clients via check_postgres, and it's been very good at detecting when the replica has problems. Good enough that I've never worried about writing a different method, anyway. :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201012101126 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk0CVN4ACgkQvJuQZxSWSshqIgCZASW1Tsf+8/Mk2qYPIzYUoYZz +CwAmwQbwzv8ED1QRskG8DavSr89NG/d =qwtc -END PGP SIGNATURE- -- 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 cronjob for copying a table from Oracle
Please help, struggling since hours with this :-(
I've created the following table (columns here and in the proc
sorted alphabetically) to acquire data copied from Oracle:
# \d qtrack
Table public.qtrack
Column|Type | Modifiers
-+-+---
appsversion | character varying(30) |
beta_prog | character varying(20) |
category| character varying(120) |
catinfo | character varying(120) |
details | character varying(50) |
devinfo | character varying(4000) |
emailid | character varying(16) |
email | character varying(320) |
formfactor | character varying(10) |
id | character varying(20) | not null
imei| character varying(25) |
name| character varying(20) |
osversion | character varying(30) |
pin | character varying(12) |
qdatetime | timestamp without time zone |
copied | timestamp without time zone | default now()
Indexes:
qtrack_pkey PRIMARY KEY, btree (id)
And for my upsert procedure I get the error:
SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too
long for type character varying(16)
CONTEXT: SQL statement update qtrack set APPSVERSION = $1 ,
BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 ,
DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID =
$10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 ,
QDATETIME = $15 , COPIED = current_timestamp where ID = $10
PL/pgSQL function qtrack_upsert line 2 at SQL statement
My upsert procedure is:
create or replace function qtrack_upsert(
_APPSVERSION varchar,
_BETA_PROG varchar,
_CATEGORYvarchar,
_CATINFO varchar,
_DETAILS varchar,
_DEVINFO varchar,
_EMAILID varchar,
_EMAIL varchar,
_FORMFACTOR varchar,
_ID varchar,
_IMEIvarchar,
_NAMEvarchar,
_OSVERSION varchar,
_PIN varchar,
_QDATETIME timestamp
) returns void as $BODY$
begin
update qtrack set
APPSVERSION = _APPSVERSION,
BETA_PROG = _BETA_PROG,
CATEGORY= _CATEGORY,
CATINFO = _CATINFO,
DETAILS = _DETAILS,
DEVINFO = _DEVINFO,
EMAIL = _EMAIL,
EMAILID = _EMAILID,
FORMFACTOR = _FORMFACTOR,
ID = _ID,
IMEI= _IMEI,
NAME= _NAME,
OSVERSION = _OSVERSION,
PIN = _PIN,
QDATETIME = _QDATETIME,
COPIED = current_timestamp
where ID = _ID;
if not found then
insert into qtrack (
APPSVERSION,
BETA_PROG,
CATEGORY,
CATINFO,
DETAILS,
DEVINFO,
EMAIL,
EMAILID,
FORMFACTOR,
ID,
IMEI,
NAME,
OSVERSION,
PIN,
QDATETIME
) values (
_APPSVERSION,
_BETA_PROG,
_CATEGORY,
_CATINFO,
_DETAILS,
_DEVINFO,
_EMAIL,
_EMAILID,
_FORMFACTOR,
_ID,
_IMEI,
_NAME,
_OSVERSION,
_PIN,
_QDATETIME
);
end if;
end;
$BODY$ language plpgsql;
The weird thing is when I omit the 7th param
in my PHP code as shown below, then it works:
$sth = $pg-prepare(SQL_UPSERT);
while (($row = oci_fetch_array($stid,
OCI_NUM+OCI_RETURN_NULLS)) != false) {
$sth-execute(array(
$row[0],
$row[1],
$row[2],
$row[3],
$row[4],
$row[5],
null, #$row[6],
$row[7],
$row[8],
Re: [GENERAL] A cronjob for copying a table from Oracle
Hey Alexander,
Can you post the SQL with call of the function (SQL_UPSERT)
I guess ?
2010/12/10 Alexander Farber alexander.far...@gmail.com
Please help, struggling since hours with this :-(
I've created the following table (columns here and in the proc
sorted alphabetically) to acquire data copied from Oracle:
# \d qtrack
Table public.qtrack
Column|Type | Modifiers
-+-+---
appsversion | character varying(30) |
beta_prog | character varying(20) |
category| character varying(120) |
catinfo | character varying(120) |
details | character varying(50) |
devinfo | character varying(4000) |
emailid | character varying(16) |
email | character varying(320) |
formfactor | character varying(10) |
id | character varying(20) | not null
imei| character varying(25) |
name| character varying(20) |
osversion | character varying(30) |
pin | character varying(12) |
qdatetime | timestamp without time zone |
copied | timestamp without time zone | default now()
Indexes:
qtrack_pkey PRIMARY KEY, btree (id)
And for my upsert procedure I get the error:
SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too
long for type character varying(16)
CONTEXT: SQL statement update qtrack set APPSVERSION = $1 ,
BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 ,
DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID =
$10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 ,
QDATETIME = $15 , COPIED = current_timestamp where ID = $10
PL/pgSQL function qtrack_upsert line 2 at SQL statement
My upsert procedure is:
create or replace function qtrack_upsert(
_APPSVERSION varchar,
_BETA_PROG varchar,
_CATEGORYvarchar,
_CATINFO varchar,
_DETAILS varchar,
_DEVINFO varchar,
_EMAILID varchar,
_EMAIL varchar,
_FORMFACTOR varchar,
_ID varchar,
_IMEIvarchar,
_NAMEvarchar,
_OSVERSION varchar,
_PIN varchar,
_QDATETIME timestamp
) returns void as $BODY$
begin
update qtrack set
APPSVERSION = _APPSVERSION,
BETA_PROG = _BETA_PROG,
CATEGORY= _CATEGORY,
CATINFO = _CATINFO,
DETAILS = _DETAILS,
DEVINFO = _DEVINFO,
EMAIL = _EMAIL,
EMAILID = _EMAILID,
FORMFACTOR = _FORMFACTOR,
ID = _ID,
IMEI= _IMEI,
NAME= _NAME,
OSVERSION = _OSVERSION,
PIN = _PIN,
QDATETIME = _QDATETIME,
COPIED = current_timestamp
where ID = _ID;
if not found then
insert into qtrack (
APPSVERSION,
BETA_PROG,
CATEGORY,
CATINFO,
DETAILS,
DEVINFO,
EMAIL,
EMAILID,
FORMFACTOR,
ID,
IMEI,
NAME,
OSVERSION,
PIN,
QDATETIME
) values (
_APPSVERSION,
_BETA_PROG,
_CATEGORY,
_CATINFO,
_DETAILS,
_DEVINFO,
_EMAIL,
_EMAILID,
_FORMFACTOR,
_ID,
_IMEI,
_NAME,
_OSVERSION,
_PIN,
_QDATETIME
);
end if;
end;
$BODY$ language plpgsql;
The weird thing is when I omit the 7th param
in my PHP code as shown below, then it works:
$sth = $pg-prepare(SQL_UPSERT);
while (($row = oci_fetch_array($stid,
OCI_NUM+OCI_RETURN_NULLS)) != false) {
$sth-execute(array(
$row[0],
$row[1],
Re: [GENERAL] A cronjob for copying a table from Oracle
On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: Please help, struggling since hours with this :-( I've created the following table (columns here and in the proc sorted alphabetically) to acquire data copied from Oracle: # \d qtrack Table public.qtrack Column|Type | Modifiers -+-+--- appsversion | character varying(30) | beta_prog | character varying(20) | category| character varying(120) | catinfo | character varying(120) | details | character varying(50) | devinfo | character varying(4000) | emailid | character varying(16) | email | character varying(320) | formfactor | character varying(10) | id | character varying(20) | not null imei| character varying(25) | name| character varying(20) | osversion | character varying(30) | pin | character varying(12) | qdatetime | timestamp without time zone | copied | timestamp without time zone | default now() Indexes: qtrack_pkey PRIMARY KEY, btree (id) And for my upsert procedure I get the error: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(16) CONTEXT: SQL statement update qtrack set APPSVERSION = $1 , BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 , DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID = $10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 , QDATETIME = $15 , COPIED = current_timestamp where ID = $10 PL/pgSQL function qtrack_upsert line 2 at SQL statement Looks like you got your EMAIL and EMAILID reversed. In your argument list EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL. My upsert procedure is: create or replace function qtrack_upsert( _APPSVERSION varchar, _BETA_PROG varchar, _CATEGORYvarchar, _CATINFO varchar, _DETAILS varchar, _DEVINFO varchar, _EMAILID varchar, _EMAIL varchar, _FORMFACTOR varchar, _ID varchar, _IMEIvarchar, _NAMEvarchar, _OSVERSION varchar, _PIN varchar, _QDATETIME timestamp ) returns void as $BODY$ begin update qtrack set APPSVERSION = _APPSVERSION, BETA_PROG = _BETA_PROG, CATEGORY= _CATEGORY, CATINFO = _CATINFO, DETAILS = _DETAILS, DEVINFO = _DEVINFO, EMAIL = _EMAIL, EMAILID = _EMAILID, FORMFACTOR = _FORMFACTOR, ID = _ID, IMEI= _IMEI, NAME= _NAME, OSVERSION = _OSVERSION, PIN = _PIN, QDATETIME = _QDATETIME, COPIED = current_timestamp where ID = _ID; if not found then insert into qtrack ( APPSVERSION, BETA_PROG, CATEGORY, CATINFO, DETAILS, DEVINFO, EMAIL, EMAILID, FORMFACTOR, ID, IMEI, NAME, OSVERSION, PIN, QDATETIME ) values ( _APPSVERSION, _BETA_PROG, _CATEGORY, _CATINFO, _DETAILS, _DEVINFO, _EMAIL, _EMAILID, _FORMFACTOR, _ID, _IMEI, _NAME, _OSVERSION, _PIN, _QDATETIME ); end if; end; $BODY$ language plpgsql; The weird thing is when I omit the 7th param in my PHP code as shown below, then it works: $sth = $pg-prepare(SQL_UPSERT); while (($row =
Re: [GENERAL] A cronjob for copying a table from Oracle
Hey Adrian, 2010/12/10 Adrian Klaver adrian.kla...@gmail.com On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: Please help, struggling since hours with this :-( I've created the following table (columns here and in the proc sorted alphabetically) to acquire data copied from Oracle: # \d qtrack Table public.qtrack Column|Type | Modifiers -+-+--- appsversion | character varying(30) | beta_prog | character varying(20) | category| character varying(120) | catinfo | character varying(120) | details | character varying(50) | devinfo | character varying(4000) | emailid | character varying(16) | email | character varying(320) | formfactor | character varying(10) | id | character varying(20) | not null imei| character varying(25) | name| character varying(20) | osversion | character varying(30) | pin | character varying(12) | qdatetime | timestamp without time zone | copied | timestamp without time zone | default now() Indexes: qtrack_pkey PRIMARY KEY, btree (id) And for my upsert procedure I get the error: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(16) CONTEXT: SQL statement update qtrack set APPSVERSION = $1 , BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 , DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID = $10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 , QDATETIME = $15 , COPIED = current_timestamp where ID = $10 PL/pgSQL function qtrack_upsert line 2 at SQL statement Looks like you got your EMAIL and EMAILID reversed. In your argument list EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL. Yes, but he refers arguments by name, rather than number. UPDATE statement seems to be correct in the function definition. My upsert procedure is: create or replace function qtrack_upsert( _APPSVERSION varchar, _BETA_PROG varchar, _CATEGORYvarchar, _CATINFO varchar, _DETAILS varchar, _DEVINFO varchar, _EMAILID varchar, _EMAIL varchar, _FORMFACTOR varchar, _ID varchar, _IMEIvarchar, _NAMEvarchar, _OSVERSION varchar, _PIN varchar, _QDATETIME timestamp ) returns void as $BODY$ begin update qtrack set APPSVERSION = _APPSVERSION, BETA_PROG = _BETA_PROG, CATEGORY= _CATEGORY, CATINFO = _CATINFO, DETAILS = _DETAILS, DEVINFO = _DEVINFO, EMAIL = _EMAIL, EMAILID = _EMAILID, FORMFACTOR = _FORMFACTOR, ID = _ID, IMEI= _IMEI, NAME= _NAME, OSVERSION = _OSVERSION, PIN = _PIN, QDATETIME = _QDATETIME, COPIED = current_timestamp where ID = _ID; if not found then insert into qtrack ( APPSVERSION, BETA_PROG, CATEGORY, CATINFO, DETAILS, DEVINFO, EMAIL, EMAILID, FORMFACTOR, ID, IMEI, NAME, OSVERSION, PIN, QDATETIME ) values ( _APPSVERSION, _BETA_PROG, _CATEGORY, _CATINFO, _DETAILS, _DEVINFO, _EMAIL, _EMAILID, _FORMFACTOR, _ID, _IMEI, _NAME, _OSVERSION, _PIN,
Re: [GENERAL] monitoring warm standby lag in 8.4?
Yeah, my website is busted. I'll fix it for you. On Thu, Dec 9, 2010 at 2:30 PM, Josh Kupershmidt schmi...@gmail.com wrote: Hi all, I'm wondering if there's an accepted way to monitor a warm standby machine's lag in 8.4. The wiki[1] has a link[2] to a script which parses the output of pg_controldata, looking for a line like this: Time of latest checkpoint: Thu 09 Dec 2010 01:35:46 PM EST But I'm not sure whether this timestamp is to be trusted as an indicator of how far behind the standby is in its recovery -- this timestamp just tells us when the standby last performed a checkpoint, regardless of how far behind in the WAL stream it is, right? I haven't come across any other monitoring suggestions for warm standby on 8.4. I've seen suggestions for hot standby slaves to use: SELECT pg_last_xlog_receive_location(); but this won't work on an 8.4 warm standby of course. I've searched around and haven't found[3] any other tips on how to monitor my standby. The manual mentions[4] using pg_xlogfile_name_offset() in the context of implementing record-based log shipping. Would this be useful for monitoring standby lag? Any other ideas? Thanks, Josh -- [1] http://wiki.postgresql.org/wiki/Warm_Standby [2] http://www.kennygorman.com/wordpress/?p=249 [3] I was hoping this page would have some relevant info: http://www.scottrmead.com/blogs/scott/warm-standby-monitoring , but it's down now :( [4] http://www.postgresql.org/docs/8.4/static/warm-standby.html#WARM-STANDBY-RECORD -- 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] Extended query protocol and exact types matches.
On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin dmit...@gmail.com wrote: Hey general@, To be assured and just for calmness. Problem: 1. CREATE TABLE test_tab (id integer, dat varchar(64)); 2. INSERT INTO test_tab VALUES($1, $2) via PQexecParams, where paramTypes[0] == OID of bigint, paramTypes[1] == OID of text. Questions: Whether this case falls to http://www.postgresql.org/docs/9.0/static/typeconv-query.html ? Is such cases safe or it is recommended (best) to specify a OIDs which are exact matches ? Anyways, here's the deal: The oid vector passed to the database in these functions is for describing the data you are passing. If left NULL, you leave it up to the database to try and guess what you are sending based on the context of the query. This has pros and cons. With the text protocol, it's somewhat ok to leave off the oid vector: this isn't much different from sending uncasted unknown strings into psql. It's basically there to protect you from sending bogus data to the server and reduce chance of type confusion. If you are using binary protocol, the oid vector is absolutely essential -- it's insane to have the server 'guess' what you are passing in since a wrong guess could be interpreted improperly vs a formatting error that text casting raises. If you are wrapping libpq with a higher level library, sending the correct oids always would be a pretty good idea. Meaning, you should try and coerce your application/language types into a type the database understands and pass a corresponding oid. 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 cronjob for copying a table from Oracle
On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote: Hey Adrian, 2010/12/10 Adrian Klaver adrian.kla...@gmail.com On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: Please help, struggling since hours with this :-( I've created the following table (columns here and in the proc sorted alphabetically) to acquire data copied from Oracle: # \d qtrack Table public.qtrack Column|Type | Modifiers -+-+--- appsversion | character varying(30) | beta_prog | character varying(20) | category| character varying(120) | catinfo | character varying(120) | details | character varying(50) | devinfo | character varying(4000) | emailid | character varying(16) | email | character varying(320) | formfactor | character varying(10) | id | character varying(20) | not null imei| character varying(25) | name| character varying(20) | osversion | character varying(30) | pin | character varying(12) | qdatetime | timestamp without time zone | copied | timestamp without time zone | default now() Indexes: qtrack_pkey PRIMARY KEY, btree (id) And for my upsert procedure I get the error: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(16) CONTEXT: SQL statement update qtrack set APPSVERSION = $1 , BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 , DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID = $10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 , QDATETIME = $15 , COPIED = current_timestamp where ID = $10 PL/pgSQL function qtrack_upsert line 2 at SQL statement Looks like you got your EMAIL and EMAILID reversed. In your argument list EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL. Yes, but he refers arguments by name, rather than number. UPDATE statement seems to be correct in the function definition. I am just looking at the CONTEXT message above and it showing EMAIL being assigned the $7 variable, which according to his argument list is _EMAILID. EMAIL and EMAILID are the only two fields where the variable number does not match the variable/argument numbers and are in fact reversed. So something is happening there and would explain the problem because you that would mean you are trying to stuff a 320 char field into a 16 char slot :) My upsert procedure is: create or replace function qtrack_upsert( _APPSVERSION varchar, _BETA_PROG varchar, _CATEGORYvarchar, _CATINFO varchar, _DETAILS varchar, _DEVINFO varchar, _EMAILID varchar, _EMAIL varchar, _FORMFACTOR varchar, _ID varchar, _IMEIvarchar, _NAMEvarchar, _OSVERSION varchar, _PIN varchar, _QDATETIME timestamp ) returns void as $BODY$ begin update qtrack set APPSVERSION = _APPSVERSION, BETA_PROG = _BETA_PROG, CATEGORY= _CATEGORY, CATINFO = _CATINFO, DETAILS = _DETAILS, DEVINFO = _DEVINFO, EMAIL = _EMAIL, EMAILID = _EMAILID, FORMFACTOR = _FORMFACTOR, ID = _ID, IMEI= _IMEI, NAME= _NAME, OSVERSION = _OSVERSION, PIN = _PIN, QDATETIME = _QDATETIME, COPIED = current_timestamp where ID = _ID; if not found then insert into qtrack ( APPSVERSION, BETA_PROG, CATEGORY, CATINFO, DETAILS, DEVINFO, EMAIL, EMAILID, FORMFACTOR, ID, IMEI, NAME, OSVERSION, PIN, QDATETIME ) values (
Re: [GENERAL] Extended query protocol and exact types matches.
Hey Merlin, Thank you for explanation ! Yes, I understand that specifying NULL instead real OID will provoke the parser attempts to infer the data types in the same way as it would do for untyped literal string constants. But there are three string types: text, varchar(n) and character(n) which has a different OIDs but they are all in the same type category. So, is it worth it to implement some Varchar and Character types (which actually wraps Text) at the library level or specifying the OID of text for contexts where these parameters actually varchar or char (i.e. types of same category) are safe? 2010/12/10 Merlin Moncure mmonc...@gmail.com On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin dmit...@gmail.com wrote: Hey general@, To be assured and just for calmness. Problem: 1. CREATE TABLE test_tab (id integer, dat varchar(64)); 2. INSERT INTO test_tab VALUES($1, $2) via PQexecParams, where paramTypes[0] == OID of bigint, paramTypes[1] == OID of text. Questions: Whether this case falls to http://www.postgresql.org/docs/9.0/static/typeconv-query.html ? Is such cases safe or it is recommended (best) to specify a OIDs which are exact matches ? Anyways, here's the deal: The oid vector passed to the database in these functions is for describing the data you are passing. If left NULL, you leave it up to the database to try and guess what you are sending based on the context of the query. This has pros and cons. With the text protocol, it's somewhat ok to leave off the oid vector: this isn't much different from sending uncasted unknown strings into psql. It's basically there to protect you from sending bogus data to the server and reduce chance of type confusion. If you are using binary protocol, the oid vector is absolutely essential -- it's insane to have the server 'guess' what you are passing in since a wrong guess could be interpreted improperly vs a formatting error that text casting raises. If you are wrapping libpq with a higher level library, sending the correct oids always would be a pretty good idea. Meaning, you should try and coerce your application/language types into a type the database understands and pass a corresponding oid. merlin -- // Dmitriy.
Re: [GENERAL] A cronjob for copying a table from Oracle
Huh! Yes, indeed ! But how is it possible ?! I see EMAIL = _EMAIL, EMAILID = _EMAILID, rather than EMAIL = $7, EMAILID = $8, in the function definition... 2010/12/10 Adrian Klaver adrian.kla...@gmail.com On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote: Hey Adrian, 2010/12/10 Adrian Klaver adrian.kla...@gmail.com On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: Please help, struggling since hours with this :-( I've created the following table (columns here and in the proc sorted alphabetically) to acquire data copied from Oracle: # \d qtrack Table public.qtrack Column|Type | Modifiers -+-+--- appsversion | character varying(30) | beta_prog | character varying(20) | category| character varying(120) | catinfo | character varying(120) | details | character varying(50) | devinfo | character varying(4000) | emailid | character varying(16) | email | character varying(320) | formfactor | character varying(10) | id | character varying(20) | not null imei| character varying(25) | name| character varying(20) | osversion | character varying(30) | pin | character varying(12) | qdatetime | timestamp without time zone | copied | timestamp without time zone | default now() Indexes: qtrack_pkey PRIMARY KEY, btree (id) And for my upsert procedure I get the error: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(16) CONTEXT: SQL statement update qtrack set APPSVERSION = $1 , BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 , DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID = $10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 , QDATETIME = $15 , COPIED = current_timestamp where ID = $10 PL/pgSQL function qtrack_upsert line 2 at SQL statement Looks like you got your EMAIL and EMAILID reversed. In your argument list EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL. Yes, but he refers arguments by name, rather than number. UPDATE statement seems to be correct in the function definition. I am just looking at the CONTEXT message above and it showing EMAIL being assigned the $7 variable, which according to his argument list is _EMAILID. EMAIL and EMAILID are the only two fields where the variable number does not match the variable/argument numbers and are in fact reversed. So something is happening there and would explain the problem because you that would mean you are trying to stuff a 320 char field into a 16 char slot :) My upsert procedure is: create or replace function qtrack_upsert( _APPSVERSION varchar, _BETA_PROG varchar, _CATEGORYvarchar, _CATINFO varchar, _DETAILS varchar, _DEVINFO varchar, _EMAILID varchar, _EMAIL varchar, _FORMFACTOR varchar, _ID varchar, _IMEIvarchar, _NAMEvarchar, _OSVERSION varchar, _PIN varchar, _QDATETIME timestamp ) returns void as $BODY$ begin update qtrack set APPSVERSION = _APPSVERSION, BETA_PROG = _BETA_PROG, CATEGORY= _CATEGORY, CATINFO = _CATINFO, DETAILS = _DETAILS, DEVINFO = _DEVINFO, EMAIL = _EMAIL, EMAILID = _EMAILID, FORMFACTOR = _FORMFACTOR, ID = _ID, IMEI= _IMEI, NAME= _NAME, OSVERSION = _OSVERSION, PIN = _PIN, QDATETIME = _QDATETIME, COPIED = current_timestamp where ID = _ID; if not found then insert into qtrack ( APPSVERSION, BETA_PROG, CATEGORY, CATINFO, DETAILS,
Re: [GENERAL] A cronjob for copying a table from Oracle
On 12/10/2010 09:45 AM, Dmitriy Igrishin wrote: Huh! Yes, indeed ! But how is it possible ?! I see EMAIL = _EMAIL, EMAILID = _EMAILID, rather than EMAIL = $7, EMAILID = $8, in the function definition... My guess the reversal is taking place in the PHP code. The table definition and the argument list to the Pg function have one order for emailid,email and the update and insert statements have another; email,emailid. I would guess that the PHP is building the row variables using the SQL statement order and than passing that to the Pg function which has a different order. -- 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] A cronjob for copying a table from Oracle
2010/12/10 Adrian Klaver adrian.kla...@gmail.com On 12/10/2010 09:45 AM, Dmitriy Igrishin wrote: Huh! Yes, indeed ! But how is it possible ?! I see EMAIL = _EMAIL, EMAILID = _EMAILID, rather than EMAIL = $7, EMAILID = $8, in the function definition... My guess the reversal is taking place in the PHP code. The table definition and the argument list to the Pg function have one order for emailid,email and the update and insert statements have another; email,emailid. I would guess that the PHP is building the row variables using the SQL statement order and than passing that to the Pg function which has a different order. Yeah, thats why I've asked the OP to post SQL with call of the function (in PHP). -- Adrian Klaver adrian.kla...@gmail.com -- // Dmitriy.
Re: [GENERAL] Extended query protocol and exact types matches.
On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin dmit...@gmail.com wrote: Hey Merlin, Thank you for explanation ! Yes, I understand that specifying NULL instead real OID will provoke the parser attempts to infer the data types in the same way as it would do for untyped literal string constants. But there are three string types: text, varchar(n) and character(n) which has a different OIDs but they are all in the same type category. So, is it worth it to implement some Varchar and Character types (which actually wraps Text) at the library level or specifying the OID of text for contexts where these parameters actually varchar or char (i.e. types of same category) are safe? not really, at the end of the day, you are coming in from C char*, so just send TEXTOID and let the server worry about what to do if say you are passing into varchar or (more rarely char(n)). libpqtypes, the library you are pretending doesn't exist, does this (http://libpqtypes.esilo.com/man3/pqt-specs.html). PGtext is typedef'd char* and the only format string for character types is %text. IMNSHO, If you wanted to attack this problem in an actually novel and useful way in C++ style, I would consider taking the libpqtypes library, rip out all the format string stuff, and rig variadic templates so you could leverage variadic queries. Maybe this could be integrated into libpqxx, not sure. printf : cout :: : PQexecf : query query(conn, select $1 + $2, 3, 7); 'query' is hypothetical function that uses template type inference, mapping/marshaling data and building the data structure that PQexecParams points to (in libpqtypes, the PGparam). Parsing the type format string is expensive enough that we had to implement a client side prepare to reduce the cost of searching type handlers over and over. Of course, cout is not really faster than printf, but that's another topic :-). 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 cronjob for copying a table from Oracle
On Fri, Dec 10, 2010 at 6:15 PM, Adrian Klaver adrian.kla...@gmail.com wrote: On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(16) CONTEXT: SQL statement update qtrack set APPSVERSION = $1 , BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 , DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID = $10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 , QDATETIME = $15 , COPIED = current_timestamp where ID = $10 PL/pgSQL function qtrack_upsert line 2 at SQL statement Looks like you got your EMAIL and EMAILID reversed. In your argument list EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL. That was it Adrian, thank you so much! It was reversed in my Oracle's select and thus the PostgreSQL's upsert was failing. I was looking too many times at that spot, so I stopped really reading it. Dmitiry, $7 and $8 etc. is probably what plpgsql substitutes for _EMAIL and _EMAILID internally Regards Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using regexp_replace to remove small words
Hi, I'm trying to find a regular expression that removes all small (length N) words from a string. But, until now I've not been successful. For example: If I pass 'George W Bush' as parameter, I want regexp_replace to return 'George Bush'. Other examples are: select regexp_replace( 'Tommy Lee Jones', 'REGEX', ' ', 'g' )= 'Tommy Jones' select regexp_replace( 'Dr Tommy Lee Jones', 'REGEX', ' ', 'g' ) = 'Tommy Jones' select regexp_replace( 'Tommy Lee Jones Jr', 'REGEX', ' ', 'g' ) = 'Tommy Jones' Thanks in advance, HENRIQUE TRINDADE ANALISTA DE SISTEMA II ( 55 (31) 3025-3550 +henri...@vivver.com.br assinatura image002.jpg
Re: [GENERAL] Extended query protocol and exact types matches.
2010/12/10 Merlin Moncure mmonc...@gmail.com On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin dmit...@gmail.com wrote: Hey Merlin, Thank you for explanation ! Yes, I understand that specifying NULL instead real OID will provoke the parser attempts to infer the data types in the same way as it would do for untyped literal string constants. But there are three string types: text, varchar(n) and character(n) which has a different OIDs but they are all in the same type category. So, is it worth it to implement some Varchar and Character types (which actually wraps Text) at the library level or specifying the OID of text for contexts where these parameters actually varchar or char (i.e. types of same category) are safe? not really, at the end of the day, you are coming in from C char*, so just send TEXTOID and let the server worry about what to do if say you are passing into varchar or (more rarely char(n)). libpqtypes, the library you are pretending doesn't exist, Me ? :-) !true ! I just pretend not to bloat libpq and keep it clean... does this (http://libpqtypes.esilo.com/man3/pqt-specs.html). PGtext is typedef'd char* and the only format string for character types is %text. IMNSHO, If you wanted to attack this problem in an actually novel and useful way in C++ style, I would consider taking the libpqtypes library, rip out all the format string stuff, and rig variadic templates so you could leverage variadic queries. Maybe this could be integrated into libpqxx, not sure. printf : cout :: : PQexecf : query query(conn, select $1 + $2, 3, 7); 'query' is hypothetical function that uses template type inference, mapping/marshaling data and building the data structure that PQexecParams points to (in libpqtypes, the PGparam). Parsing the type format string is expensive enough that we had to implement a client side prepare to reduce the cost of searching type handlers over and over. Of course, cout is not really faster than printf, but that's another topic :-). I've implemented client side prepare too! :-) So, I am on right way and not alone! :-) merlin Thank you very much ! You help me a lot! -- // Dmitriy.
Re: [GENERAL] Invalid byte sequence
Was the original DB in UTF8 encoding? You need to make sure the new DB is created with the same encoding as the original, or do the necessary translations using something like iconv. -- 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] Import id column then convert to SEQUENCE?
On Thu, Dec 9, 2010 at 10:56 PM, Shoaib Mir shoaib...@gmail.com wrote: I guess I misread it... use the following: - Import all the data into say an integer column. - Now create a sequence and give it a start value of where your import ended. - Make the default value for the column using the new sequence. create the table with a SERIAL column, which will make the sequence and set the auto increment default as you wanted. import the data update the sequence with setval() to the next highest value you want it to return, usually max()+1 of the populated column. -- 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] Using regexp_replace to remove small words
On fre, 2010-12-10 at 10:47 -0200, Henrique de Lima Trindade wrote:
I'm trying to find a regular expression that removes all small (length N)
words from a string. But, until now I've not been successful.
Here is a start:
select regexp_replace('Tommy Lee Jones', $$\y\w{2,3}\y$$, ' ', 'g' );
If you want to normalize the spaces after the removal and handle
beginning and end of the word, you will need to expand this to cover
those cases, but the example should contain the key ingredients.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump order of rows
Hello, today I stumbled across a interesting question about the order rows are dumped out while exporting a database with pg_dump. I know questions like this are around this list sometimes, but I think this is a bit more special. First of all I know that dumping a database is a somewhat nondeterministic process. It's hard to determine in which order objects are dumped. If my understanding is correct when it's about dumping the rows from a specific table the rows will appear in the dump in disk-order. This order is changed everytime there are updates to rows in that table and how often a vacuum occurs to release old row versions. From some testing I verified this by experimentation - just created a new table with a known order of rows and dumped it. The dump was ordered in the same way the rows were inserted. Same again with updating some of the rows those rows appeard at the end of the dump. At last I vacuumed the database and updated some rows they appeared in the spaces the previous updated rows left behind. Exactly what I expected :-) Now for my question - we have a case where rows are inserted in order and are updated rarely (about 10% of the rows and often in order) and we are seeking for a solution to make the dumop (e.g. the backup) more random at database level (by influencing the on disk order)? Obvious way would be to update all rows randomly by software before dumpim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump order of rows
Hello, today I stumbled across a interesting question about the order rows are dumped out while exporting a database with pg_dump. I know questions like this are around this list sometimes, but I think this is a bit more special. First of all I know that dumping a database is a somewhat nondeterministic process. It's hard to determine in which order objects are dumped. If my understanding is correct when it's about dumping the rows from a specific table the rows will appear in the dump in disk-order. This order is changed everytime there are updates to rows in that table and how often a vacuum occurs to release old row versions. From some testing I verified this by experimentation - just created a new table with a known order of rows and dumped it. The dump was ordered in the same way the rows were inserted. Same again with updating some of the rows those rows appeard at the end of the dump. At last I vacuumed the database and updated some rows they appeared in the spaces the previous updated rows left behind. Exactly what I expected :-) Now for my question - we have a case where rows are inserted in order and are updated rarely (about 10% of the rows and often in order) and we are seeking for a solution to make the dump (e.g. the backup of this database) more random at database level (by influencing the on disk order?). Obvious way would be to update all rows randomly by software before dumping but that would be a manual process. So anyone out there with some other ideas (I found this interesting as I'm getting a more insight view of my favourite database)? Thanks in advance, Jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump order of rows
Hello, today I stumbled across a interesting question about the order rows are dumped out while exporting a database with pg_dump. I know questions like this are around this list sometimes, but I think this is a bit more special. First of all I know that dumping a database is a somewhat nondeterministic process. It's hard to determine in which order objects are dumped. If my understanding is correct when it's about dumping the rows from a specific table the rows will appear in the dump in disk-order. This order is changed everytime there are updates to rows in that table and how often a vacuum occurs to release old row versions. From some testing I verified this by experimentation - just created a new table with a known order of rows and dumped it. The dump was ordered in the same way the rows were inserted. Same again with updating some of the rows those rows appeard at the end of the dump. At last I vacuumed the database and updated some rows they appeared in the spaces the previous updated rows left behind. Exactly what I expected :-) Now for my question - we have a case where rows are inserted in order and are updated rarely (about 10% of the rows and often in order) and we are seeking for a solution to make the dump (e.g. the backup of this database) more random at database level (by influencing the on disk order?). Obvious way would be to update all rows randomly by software before dumping but that would be a manual process. So anyone out there with some other ideas (I found this interesting as I'm getting a more insight view of my favourite database)? Thanks in advance, Jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GiST indexing question
Hi, I'm attempting to expand an existing postgresql extension and I've run into a wall with the way operator classes should be defined for GiST indices. What I have that works is the following two operators: CREATE OPERATOR @ ( LEFTARG = mol, RIGHTARG = mol, PROCEDURE = rsubstruct(mol, mol), COMMUTATOR = '@', RESTRICT = contsel, JOIN = contjoinsel ); CREATE OPERATOR @ ( LEFTARG = mol, RIGHTARG = mol, PROCEDURE = substruct(mol, mol), COMMUTATOR = '@', RESTRICT = contsel, JOIN = contjoinsel ); combined into an operator class for use in indexing : CREATE OPERATOR CLASS mol_ops DEFAULT FOR TYPE mol USING gist AS OPERATOR3 @ (mol, mol), OPERATOR4 @ (mol, mol), FUNCTION1 gmol_consistent (bytea, internal, int4), FUNCTION2 gmol_union (bytea, internal), FUNCTION3 gmol_compress (internal), FUNCTION4 gmol_decompress (internal), FUNCTION5 gmol_penalty (internal, internal, internal), FUNCTION6 gmol_picksplit (internal, internal), FUNCTION7 gmol_same (bytea, bytea, internal), STORAGE bytea; I'm now trying to add an equality operator (==) as follows: CREATE OPERATOR == ( LEFTARG = mol, RIGHTARG = mol, PROCEDURE = mol_eq, COMMUTATOR = '=', NEGATOR = '', RESTRICT = eqsel, JOIN = eqjoinsel ); and I want to use this to extend the operator class: CREATE OPERATOR CLASS mol_ops DEFAULT FOR TYPE mol USING gist AS OPERATOR3 @ (mol, mol), OPERATOR4 @ (mol, mol), OPERATOR6 == (mol, mol), FUNCTION1 gmol_consistent (bytea, internal, int4), FUNCTION2 gmol_union (bytea, internal), FUNCTION3 gmol_compress (internal), FUNCTION4 gmol_decompress (internal), FUNCTION5 gmol_penalty (internal, internal, internal), FUNCTION6 gmol_picksplit (internal, internal), FUNCTION7 gmol_same (bytea, bytea, internal), STORAGE bytea; I made something of a guess as to which strategy I should use. I can now do basic tests with my == operator: moltest=# select 'c1nnccc1'::mol=='c1ccnnc1'::mol; ?column? -- t (1 row) moltest=# select 'c1nnccc1'::mol=='c1cnnnc1'::mol; ?column? -- f (1 row) (yes, those are both correct). I can create an index using these definitions: moltest=# create index molidx on mols using gist(m); CREATE INDEX And as far as I can tell it looks like the index should be working: moltest=# explain select count(*) from mols where m=='c1ncnnc1'; QUERY PLAN - Aggregate (cost=8.27..8.28 rows=1 width=0) - Index Scan using molidx on mols (cost=0.00..8.27 rows=1 width=0) Index Cond: (m == 'c1cnncn1'::mol) (3 rows) But when I actually run a query I get an error: moltest=# select count(*) from mols where m=='c1ncnnc1'; ERROR: Unknown strategy: 6 The index isn't totally screwed up though, because an @ query still works: moltest=# select count(*) from mols where m@'c1ncnnc1'; count --- 5 (1 row) I guess I must be doing something stupid, but neither the docs nor the internet have been particularly helpful in letting me know what. These tests are all being done using postgresql 9.0.1 built on an ubuntu 10.10 box. In case it's helpful, the full extension code (without the non-functioning attempts at adding == to the index) is here: http://rdkit.svn.sourceforge.net/viewvc/rdkit/trunk/Code/PgSQL/rdkit/ Thanks in advance for any help, -greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
