Re: [GENERAL] How can I calculate differences between values
Hi; also you can try this query which should work for any version SELECT a.userid, a.data, max(f.data), a.data - max(f.data) FROM (SELECT userid, data FROM foo EXCEPT SELECT userid, min(data) FROM foo GROUP BY userid) a LEFT JOIN foo f ON (f.userid = a.userid AND f.data < a.data) GROUP BY a.userid, a.data ORDER BY a.userid, a.data A B wrote: With 8.4's analytic capabilities you can do this: select * from ( select userid, data - lag(data) over (partition by userid order by data) diff from foo) q where diff is not null; Thank you! That worked perfectly!
Re: [GENERAL] Numeric Type and VB/ODBC
You can try If (IsNumeric(varToTest) And 0 = CDbl(varToTest)) Then IsNothing = True Bret wrote: This may not be the right group, if so, just let me know. I have a table with a type [numeric]. When executing queries, I get the data which happens to be (6.5) in this case, but my VB6 function which traps for nulls (below) returns a null. If I change it to type [real]. No problems Function IsNothing(varToTest As Variant) As Integer ' Tests for a "logical" nothing based on data type ' Empty and Null = Nothing ' Number = 0 is Nothing ' Zero length string is Nothing ' Date/Time is never Nothing IsNothing = True Select Case VarType(varToTest) Case vbEmpty Exit Function Case vbNull Exit Function Case vbBoolean If varToTest Then IsNothing = False Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency If varToTest <> 0 Then IsNothing = False Case vbDate IsNothing = False Case vbString If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing = False End Select End Function Bret Stern -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] real - integer type cast in prepared statements
Hi!
Why when I prepare statement by parsing such query: 'SELECT * FROM
"test" WHERE "ind" < $1 + 1' ("ind" is of type REAL) $1 is interpreted
by backend as INTEGER? Parse completed successfully, but trying to bind
parameter as '20.20' resulted in "ERROR C22P02 Minvalid input syntax for
integer: "20.20" Fnumutils.c L98 Rpg_atoi"? I understand that it's
possible to make query like 'SELECT * FROM "test" WHERE "ind" < $1::real
+ 1', but at the query building time I can't know what type this column is!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] master/master replication with load balancer in front
(I've used other DBs in the past, but I'm fairly new to PG.) Currently I have a single PG 9.3 instance in the cloud. A Python script run as a cron job is connecting to it over the network and is doing the batch updates every hour, usually in append mode. Users have various custom scripts which are used for analytics queries and connect to the single 9.3 instance over the network and run their queries a few times a day. Dataset is a few dozen GB. The single instance scheme is not very reliable. I need to build a new DB backend. I'll set up Postgres 9.4. Ideally, I'd like to setup 2 instances, each instance placed in a different availability zone. Master/master replication. I'll put a load balancer (ELB) in front of both instances. The batch updates and the queries will be sent by the ELB to any instance in the cluster; replication will take care of copying the data to all instances. I want the whole cluster + the load balancer to act as a single instance to everyone connecting to it. "Eventually consistent" replication is fine. I don't want to share storage between PG instances if I can avoid it. I would like to use the 9.4.4 packages made for Ubuntu if at all possible (avoiding any patching). I see there are many different ways to build a PG cluster. What would be the best choice in my case? If I were to drop the master/master requirement and just do master/slave, sending updates to one node, and doing all analytics on the other node, what would be the best replication technique in this case? (We are also considering a migration from the batch update model to a more continuous stream.) -- Florin Andrei http://florin.myip.org/ -- 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] master/master replication with load balancer in front
On 2015-08-23 06:56, Martín Marqués wrote: El 21/08/15 a las 20:45, Florin Andrei escribió: The single instance scheme is not very reliable. I need to build a new DB backend. I'll set up Postgres 9.4. Ideally, I'd like to setup 2 instances, each instance placed in a different availability zone. Master/master replication. I'll put a load balancer (ELB) in front of both instances. It's not clear if the main goal is reliability (or availability), or to balance writes. If you are looking for HA, single master with multiple standbys is your best bet (you can put standbys on different zones). This is for reliability / availability. The thing is, if I have a single master and an AZ fails, I still have to make manual changes to switch to the healthy AZ - and, until then, updates would fail. Master/master, in theory, should absorb single-AZ failures without needing any manual intervention. You can also look at BDR and have masters geographically distributed, but I'd strongly suggest you look at the link http://bdr-project.org/docs/stable/weak-coupled-multimaster.html, and keep in mind that with multi-master systems you will be more prone to data modification conflicts. I get the sense that BDR is not 100% ready for prime time. Is that accurate? How about Bucardo? https://bucardo.org/wiki/Bucardo If I were to drop the master/master requirement and just do master/slave, sending updates to one node, and doing all analytics on the other node, what would be the best replication technique in this case? Stream replication seems the one which might fit better. Trigger based replication would choke on large bulk loads (unless you split them up into smaller pieces) It's all bulk uploads for now. It's a Python script that wakes up once in a while and dumps more data into the DB. Size varies but it can be big. -- Florin Andrei http://florin.myip.org/ -- 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] master/master replication with load balancer in front
On 2015-08-22 03:05, Chris Mair wrote:
a few keywords in your mail hint at the fact you're using AWS?
If that's the case, you might want to look into their managed
PostgreSQL hosting: it's called Amazon RDS for PostgreSQL and
supports failover ("Multi AZ") and master-slave replication
("Read Replicas").
Yes, it's AWS. A few issues with that: lack of master/master support
which you've mentioned, and we have longer term plans to use other cloud
providers as well - so I'm trying to avoid provider lock-in (whenever it
makes sense to do so).
As load balancer, PgPool-II might be what you're looking for.
Would there be any issues if I just used an ELB pointing at two masters?
Let's say I use sticky sessions to avoid clients switching masters too
much.
--
Florin Andrei
http://florin.myip.org/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BDR: cannot remove node from group
Testing BDR for the first time, using the binary packages for Ubuntu
10.04 provided at http://packages.2ndquadrant.com/bdr/apt/
Postgres 9.4.4 and BDR 0.9.2 (I think)
I'm loosely following this document:
http://bdr-project.org/docs/stable/quickstart-enabling.html
Except I've created two separate instances and I'm trying to replicate
between instances.
I've created the bdrdemo database, and then I've created the extensions:
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
Then I did bdr_group_create on one node:
SELECT bdr.bdr_group_create(
local_node_name := 'pg-test1-dev-uswest2-aws',
node_external_dsn := 'port=5432 dbname=bdrdemo'
);
But then I've realized I need a host statement in node_external_dsn. So
now I'm trying to remove this node:
SELECT bdr.bdr_part_by_node_names('{pg-test1-dev-uswest2-aws}');
But if I try to re-add it with the new parameters:
SELECT bdr.bdr_group_create(
local_node_name := 'pg-test1-dev-uswest2-aws',
node_external_dsn := 'host=pg-test1-dev-uswest2-aws port=5432
dbname=bdrdemo'
);
I get this:
ERROR: This node is already a member of a BDR group
HINT: Connect to the node you wish to add and run bdr_group_join from
it instead
What do I need to do to start over? I want to delete all traces of the
BDR configuration I've done so far.
--
Florin Andrei
http://florin.myip.org/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] avoid lock conflict between SELECT and TRUNCATE
Once in a while, I have a report running a complex query such as this:
BEGIN;declare "SQL_CUR04919850" cursor with hold for SELECT
"auths_with_trans"."user_id" AS "user_id (auths_with_trans)",
MAX("auths_with_trans"."user_created") AS
"TEMP(attr:user_created:ok)(2099950671)(0)",
MIN("auths_with_trans"."user_created") AS
"TEMP(attr:user_created:ok)(99676510)(0)",
MIN("auths_with_trans"."trans_time") AS
"usr:Calculation_6930907163324031:ok",
MIN("auths_with_trans"."auth_created") AS
"usr:Calculation_9410907163052141:ok"
FROM "public"."users" "users"
LEFT JOIN "public"."auths_with_trans" "auths_with_trans" ON
("users"."user_id" = "auths_with_trans"."user_id")
GROUP BY 1;fetch 100 in "SQL_CUR04919850"
But it takes a long time to complete, and meanwhile a cron job tries to
rebuild the users table by first doing "TRUNCATE TABLE users" and then
repopulating it with data. Obviously, TRUNCATE is blocked until the long
SELECT finishes.
I'm looking for ways to avoid the conflict. One way would be to do
incremental updates to the users table - that's not an option yet.
What if I rename the users table to users_MMDD? Would that still be
blocked by SELECT? If it's not blocked, then I could rename users out of
the way, and then recreate it with fresh data as plain 'users'. Then I'd
have a cron job dropping old users tables when they get too old.
--
Florin Andrei
http://florin.myip.org/
--
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] BDR: cannot remove node from group
On 2015-08-25 18:29, Craig Ringer wrote: On 26 August 2015 at 07:19, Florin Andrei wrote: What do I need to do to start over? I want to delete all traces of the BDR configuration I've done so far. you need to DROP the database you removed, then re-create it as a new empty database. You cannot re-join a node that has been removed. postgres=# DROP DATABASE bdrdemo; ERROR: database "bdrdemo" is being accessed by other users DETAIL: There is 1 other session using the database. Something's holding it open, not sure exactly what. -- Florin Andrei http://florin.myip.org/ -- 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] BDR: cannot remove node from group
On 2015-09-15 16:45, Florin Andrei wrote: On 2015-08-25 18:29, Craig Ringer wrote: On 26 August 2015 at 07:19, Florin Andrei wrote: What do I need to do to start over? I want to delete all traces of the BDR configuration I've done so far. you need to DROP the database you removed, then re-create it as a new empty database. You cannot re-join a node that has been removed. postgres=# DROP DATABASE bdrdemo; ERROR: database "bdrdemo" is being accessed by other users DETAIL: There is 1 other session using the database. Something's holding it open, not sure exactly what. More specifically, it seems like it's the bgworker that's holding that DB: 1123 ?S 0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf 1124 ?Ss 0:00 \_ postgres: logger process 1126 ?Ss 0:00 \_ postgres: checkpointer process 1127 ?Ss 0:00 \_ postgres: writer process 1128 ?Ss 0:00 \_ postgres: wal writer process 1129 ?Ss 0:00 \_ postgres: autovacuum launcher process 1130 ?Ss 0:00 \_ postgres: stats collector process 1136 ?Ss 0:00 \_ postgres: bgworker: bdr supervisor 1137 ?Ss 0:00 \_ postgres: bgworker: bdr db: bdrdemo Should I just kill that process, or is there a "nicer" way to do it? More generally, is there a way to just turn off BDR entirely on one node? I can't find a clear answer in the documentation to questions like - how do I turn on or off replication altogether? -- Florin Andrei http://florin.myip.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BDR: cannot drop database even after parting the node
postgres=# SELECT bdr.bdr_version();
bdr_version
---
0.9.2-2015-07-24-
(1 row)
I've tested BDR with one database on two nodes and it worked well. I've
created the group on node1 like this:
SELECT bdr.bdr_group_create(
local_node_name := 'node1',
node_external_dsn := 'node1 dbname=bdrdemo'
);
SELECT bdr.bdr_node_join_wait_for_ready();
I've then joined node2 like this:
SELECT bdr.bdr_group_join(
local_node_name := 'node2',
node_external_dsn := 'host=node2 dbname=bdrdemo',
join_using_dsn := 'host=node1 dbname=bdrdemo'
);
SELECT bdr.bdr_node_join_wait_for_ready();
I did a variety of transactions both ways, cross-checked the nodes,
everything was fine.
Then, from node1, I've parted node2 like this:
SELECT bdr.bdr_part_by_node_names('{node2}');
And then also on node1 I've parted node1 like this:
SELECT bdr.bdr_part_by_node_names('{node1}');
Now I want to start over with a clean slate, so I want to drop the
bdrdemo database on node1. But I can't:
postgres=# DROP DATABASE bdrdemo;
ERROR: database "bdrdemo" is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# SELECT pid FROM pg_stat_activity where pid <>
pg_backend_pid();
pid
---
10259
10260
(2 rows)
# ps ax | grep -e 10259 -e 10260 | grep -v grep
10259 ?Ss 0:00 postgres: bgworker: bdr supervisor
10260 ?Ss 0:00 postgres: bgworker: bdr db: bdrdemo
If I kill those workers and then drop the database, the workers get
respawned, and then the logs fill up with complaints from the workers
that they can't find the bdrdemo database.
Is there a way to stop BDR completely, so that those workers are laid to
rest and never respawn?
Basically, how do I reset BDR completely? It seems to retain the memory
of the bdrdemo database somewhere.
--
Florin Andrei
http://florin.myip.org/
--
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] BDR: cannot drop database even after parting the node
With all nodes parted from the group, I've tried to remove the bdr extension, or the table I've used for tests, but neither works: bdrdemo=# DROP EXTENSION bdr; ERROR: No peer nodes or peer node count unknown, cannot acquire DDL lock HINT: BDR is probably still starting up, wait a while bdrdemo=# DROP TABLE t1bdr; ERROR: No peer nodes or peer node count unknown, cannot acquire DDL lock HINT: BDR is probably still starting up, wait a while -- Florin Andrei http://florin.myip.org/ -- 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] BDR problem
On 2015-09-14 13:32, Martín Marqués wrote:
We (well, actually mostly you ;)) have pushed 0.9.2 bdr packages in rpm
and deb format.
$ rpm -qa | grep bdr94-bdr
postgresql-bdr94-bdr-debuginfo-0.9.2-1_2ndQuadrant.el7.centos.x86_64
postgresql-bdr94-bdr-0.9.2-1_2ndQuadrant.el7.centos.x86_64
Yup, I'm using .deb packages from
http://packages.2ndquadrant.com/bdr/apt/ on Ubuntu 14.04:
# dpkg -l | grep postgresql-bdr | awk '{print $2"\t"$3}'
postgresql-bdr-9.4 9.4.4-1trusty
postgresql-bdr-9.4-bdr-plugin 0.9.2-1trusty
postgresql-bdr-client-9.4 9.4.4-1trusty
postgresql-bdr-contrib-9.4 9.4.4-1trusty
postgresql-bdr-server-dev-9.4 9.4.4-1trusty
It's very useful to have these packages available, helps a lot with
testing, kudos to everyone involved.
--
Florin Andrei
http://florin.myip.org/
--
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] BDR: cannot drop database even after parting the node
This procedure seems to work for dismantling the replication cluster
after I'm done and cleaning up test databases. If there might be any
issues with these steps, please let me know. Thanks.
-- Disconnect node2 from cluster
--
-- on node1 run:
SELECT bdr.bdr_part_by_node_names('{node2}');
-- on node2 run:
BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
SECURITY LABEL FOR 'bdr' ON DATABASE bdrdemo IS '{"bdr": false}';
COMMIT;
-- On node2 restart PG service.
-- Now it's disconnected.
-- To drop the test database on node2, run:
DROP DATABASE bdrdemo;
-- Convert node1 to standalone, then clean slate.
--
-- Show replication slots:
SELECT * FROM pg_catalog.pg_replication_slots;
-- Drop any slots listed there:
-- SELECT pg_drop_replication_slot('slot_name');
TRUNCATE TABLE bdr.bdr_nodes;
TRUNCATE TABLE bdr.bdr_connections;
BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
SECURITY LABEL FOR 'bdr' ON DATABASE bdrdemo IS '{"bdr": false}';
COMMIT;
-- Restart PG service.
-- To drop test database:
DROP DATABASE bdrdemo;
--
Florin Andrei
http://florin.myip.org/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] bdr-plugin packages repo not updated to 0.9.3?
http://packages.2ndquadrant.com/bdr/apt/pool/main/b/bdr-plugin/ Looks like the repo has not caught up to the latest 0.9.3 release. Would it be possible to push updated packages to the repo, please? It would be a great help with testing BDR. Thanks, -- Florin Andrei http://florin.myip.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BDR: name conflict when joining a rebuilt node
Let's say node pg12 in a cluster needs to be removed because it has
serious problems. I remove it by running this command on another node in
the cluster:
SELECT bdr.bdr_part_by_node_names('{pg12}');
On pg12, I run this:
BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
SECURITY LABEL FOR 'bdr' ON DATABASE pgmirror IS '{"bdr": false}';
COMMIT;
I repair the broken node, drop the existing database, fix whatever is
wrong with it, re-create the database (empty). It's basically like a new
node. Then I try to re-join it to the cluster under the same old name:
SELECT bdr.bdr_group_join(
local_node_name := 'pg12',
node_external_dsn := 'host=pg12 dbname=pgmirror',
join_using_dsn := 'host=pg11 dbname=pgmirror'
);
SELECT bdr.bdr_node_join_wait_for_ready();
The problem is, bdr_node_join_wait_for_ready() never returns, it just
waits forever. If I go on pg11 and run SELECT * FROM bdr.bdr_nodes, I
see pg12 listed twice, with node_status k and i, respectively. On pg11 I
see this in the logs:
"System identification mismatch between connection and slot","Connection
for bdr (6211167104388615363,1,16387,) resulted in slot on node bdr
(6211167104388615363,1,17163,) instead of expected node""bdr
(6211167104388615363,1,17163,): perdb"
How can I re-join an old node to the cluster after rebuilding it from
scratch, under the old name?
Do I have to change the name every time I re-join a node?
--
Florin Andrei
http://florin.myip.org/
--
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] BDR: name conflict when joining a rebuilt node
Still having issues with this with BDR-0.9.3
This is how I join a new node to the cluster:
su - postgres
psql pgmirror
-- fire up BDR extensions
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
-- join BDR group via an existing node there
SELECT bdr.bdr_group_join(
local_node_name := 'pg12-prod-uswest2-aws',
node_external_dsn := 'host=pg12-prod-uswest2-aws dbname=pgmirror',
join_using_dsn := 'host=pg11-prod-uswest2-aws dbname=pgmirror'
);
SELECT bdr.bdr_node_join_wait_for_ready();
This is how I remove a node from the cluster:
# Log into any other node in the cluster (NOT the node you want to
remove) and run:
su - postgres
psql pgmirror
SELECT bdr.bdr_part_by_node_names('{pg12-prod-uswest2-aws}');
# Log into the removed node and run:
su - postgres
psql pgmirror
BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
SECURITY LABEL FOR 'bdr' ON DATABASE pgmirror IS '{"bdr": false}';
COMMIT;
# Now restart PostgreSQL.
Now let's say on the removed node I've dropped the pgmirror database,
performed some maintenance, re-created the pgmirror DB (empty), and now
I want to re-join the node to the cluster under the same name. I repeat
the join new node procedure described at the top. It gets stuck in
node_join_wait_for_ready().
On another node, the re-joined node is now listed twice in
bdr.bdr_nodes, once with status k, and again with status i. The logs on
the re-joined node show this:
2015-11-03 20:29:52.016 UTC,,,4916,,56391614.1334,219,,2015-11-03
20:16:20 UTC,,0,LOG,0,"starting background worker process ""bdr db:
pgmirror""",""
2015-11-03 20:29:52.047 UTC,,,7222,"",56391940.1c36,1,"",2015-11-03
20:29:52 UTC,,0,LOG,0,"connection received: host=127.0.0.1
port=21241",""
2015-11-03 20:29:52.050
UTC,"postgres","pgmirror",7222,"127.0.0.1:21241",56391940.1c36,2,"authentication",2015-11-03 20:29:52 UTC,4/321,0,LOG,0,"replication connection authorized: user=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)",""
2015-11-03 20:29:52.052 UTC,,,7221,,56391940.1c35,1,,2015-11-03 20:29:52
UTC,3/0,0,ERROR,55000,"System identification mismatch between connection
and slot","Connection for bdr (6212727469166484615,1,16387,) resulted in
slot on node bdr (6212727469166484615,1,17169,) instead of expected
node""bdr (6212727469166484615,1,17169,): perdb"
2015-11-03 20:29:52.053
UTC,"postgres","pgmirror",7222,"127.0.0.1:21241",56391940.1c36,3,"idle",2015-11-03 20:29:52 UTC,4/0,0,LOG,08006,"could not receive data from client: Connection reset by peer","bdr (6212727469166484615,1,17169,):mkslot"
2015-11-03 20:29:52.053
UTC,"postgres","pgmirror",7222,"127.0.0.1:21241",56391940.1c36,4,"idle",2015-11-03 20:29:52 UTC,,0,LOG,0,"disconnection: session time: 0:00:00.006 user=postgres database=pgmirror host=127.0.0.1 port=21241","bdr (6212727469166484615,1,17169,):mkslot"
2015-11-03 20:29:52.053 UTC,,,4916,,56391614.1334,220,,2015-11-03
20:16:20 UTC,,0,LOG,0,"worker process: bdr db: pgmirror (PID 7221)
exited with exit code 1",""
OS is Ubuntu 14.04, with these packages installed:
ii postgresql-bdr-9.4 9.4.4-1trusty
amd64object-relational SQL database, version 9.4 server
ii postgresql-bdr-9.4-bdr-plugin0.9.3-1trusty
amd64BDR Plugin for PostgreSQL-BDR 9.4
ii postgresql-bdr-client-9.49.4.4-1trusty
amd64front-end programs for PostgreSQL-BDR 9.4
ii postgresql-bdr-contrib-9.4 9.4.4-1trusty
amd64additional facilities for PostgreSQL
ii postgresql-bdr-server-dev-9.49.4.4-1trusty
amd64development files for PostgreSQL-BDR 9.4 server-side
programming
ii postgresql-client-common 169.pgdg14.04+1
all manager for multiple PostgreSQL client versions
ii postgresql-common169.pgdg14.04+1
all PostgreSQL database-cluster manager
--
Florin Andrei
http://florin.myip.org/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BDR: SSL error: bad write retry
BDR-0.9.3 and PG-9.4.4 on Ubuntu 14.04 Two nodes, BDR replication. Cluster is newly created, no nodes have been removed from it. Making/deleting small tables works well across the cluster. Now I'm trying to pg_restore a larger database from another system (pg_dump output file is 3.1 GB compressed). I am running pg_restore on one node in the cluster, and hoping that BDR would replicate changes to the other node. It went well through creating schemas, extensions, comments, views, functions, sequences and tables. It imported data into tables. Now it's trying to create indexes - but it takes a very long time and I see errors in the logs. I see periodic data transfers between instances. CPU usage shoots up, then back down. There's a cycle of a few minutes where these things occur. On the instance where I'm running pg_restore, I see this in the logs: ### 2015-11-03 21:45:35.328 UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,5,"idle",2015-11-03 21:41:58 UTC,5/146,0,LOG,08P01,"SSL error: bad write retry","slot ""bdr_16387_6212727469166484615_1_16387__"", output plugin ""bdr"", in the change callback, associated LSN 0/2B29E50""bdr (6212727469166484615,1,16387,):receive" 2015-11-03 21:45:35.328 UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,6,"idle",2015-11-03 21:41:58 UTC,5/146,0,LOG,08006,"could not receive data from client: Connection reset by peer","slot ""bdr_16387_6212727469166484615_1_16387__"", output plugin ""bdr"", in the change callback, associated LSN 0/2B29E50""bdr (6212727469166484615,1,16387,):receive" 2015-11-03 21:45:35.328 UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,7,"idle",2015-11-03 21:41:58 UTC,5/146,0,LOG,08P01,"unexpected EOF on standby connection","slot ""bdr_16387_6212727469166484615_1_16387__"", output plugin ""bdr"", in the change callback, associated LSN 0/2B29E50""bdr (6212727469166484615,1,16387,):receive" 2015-11-03 21:45:35.330 UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,8,"idle",2015-11-03 21:41:58 UTC,,0,LOG,0,"disconnection: session time: 0:03:37.234 user=postgres database=pgmirror host=10.1.1.169 port=36334","bdr (6212727469166484615,1,16387,):receive" 2015-11-03 21:45:40.421 UTC,,,5066,"",56392b04.13ca,1,"",2015-11-03 21:45:40 UTC,,0,LOG,0,"connection received: host=10.1.1.169 port=36335","" 2015-11-03 21:45:40.427 UTC,"postgres","pgmirror",5066,"10.1.1.169:36335",56392b04.13ca,2,"authentication",2015-11-03 21:45:40 UTC,5/149,0,LOG,0,"replication connection authorized: user=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)","" 2015-11-03 21:45:40.431 UTC,"postgres","pgmirror",5066,"10.1.1.169:36335",56392b04.13ca,3,"idle",2015-11-03 21:45:40 UTC,5/0,0,LOG,0,"starting logical decoding for slot ""bdr_16387_6212727469166484615_1_16387__""","streaming transactions committing after 1/14FEDFE0, reading WAL from 0/187AFA0""bdr (6212727469166484615,1,16387,):receive" 2015-11-03 21:45:40.435 UTC,"postgres","pgmirror",5066,"10.1.1.169:36335",56392b04.13ca,4,"idle",2015-11-03 21:45:40 UTC,5/0,0,LOG,0,"logical decoding found consistent point at 0/187AFA0","Logical decoding will begin using saved snapshot.""bdr (6212727469166484615,1,16387,):receive" ### On the other node in the cluster (supposed to receive the data via BDR) I see this in the logs: ### 2015-11-03 21:45:31.885 UTC,,,3391,,56391e57.d3f,19,,2015-11-03 20:51:35 UTC,,0,LOG,0,"checkpoint starting: xlog","" 2015-11-03 21:45:35.400 UTC,,,4773,,56392a26.12a5,1,,2015-11-03 21:41:58 UTC,3/201,876,ERROR,XX000,"connection to other side has died","bdr (6212727469166484615,1,16387,): apply" 2015-11-03 21:45:35.408 UTC,,,3388,,56391e56.d3c,34,,2015-11-03 20:51:34 UTC,,0,LOG,0,"worker process: bdr (6212727469166484615,1,16387,)->bdr (6212727476664052871,1, (PID 4773) exited with exit code 1","" 2015-11-03 21:45:40.412 UTC,,,3388,,56391e56.d3c,35,,2015-11-03 20:51:34 UTC,,0,LOG,0,"starting background worker process ""bdr (6212727469166484615,1,16387,)->bdr (6212727476664052871,1,""","" ### -- Florin Andrei http://florin.myip.org/ -- 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] BDR: SSL error: bad write retry
Nevermind, this was fixed with: ssl_renegotiation_limit = 0 -- Florin Andrei http://florin.myip.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] loading data into cluster - can I daisy-chain streaming replication?
I have an old production instance, let's call it A, that I need to decommission soon. I've created a pair of new instances, B and C, with B replicating to C, following this procedure: https://wiki.postgresql.org/wiki/Streaming_Replication But B and C have no data yet. I need to transfer all data from A into B. Can I daisy-chain streaming replication? In other words, temporarily setup replication like this: A ==> B ==> C And after the transfer is done, just remove A from the scheme. -- Florin Andrei http://florin.myip.org/ -- 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] problems with SELECT query results
Joshua wrote: I checked the table and found that none of my fields in the SELECT statement contain NULLs. Any other suggestions? Why are you using such constructions in your query: ',' || ',' || ',' ? May be this set of commas makes you think that some of your fields are empty? Do you have empty fields in following query? SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' || round(qm5) || ',' || round(lsm4) || ',' || round(onorder) || ',' || binone || ',' || round(backorderqty) || ',' || round(onhold) || ',' || round(qtyperjob) || ',' || round(ordermax) AS gmrim FROM slparts WHERE vendor LIKE 'CH%' P.S. If you really need so many commas - use them in a single block ',,,' PFC wrote: SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' || round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' || round(onorder) || ',' || ',' || ',' || binone || ',' || ',' || round(backorderqty) || ',' || ',' || round(onhold) || ',' || ',' || ',' || ',' || ',' || ',' || ',' || round(qtyperjob) || ',' || round(ordermax) AS gmrim FROM slparts WHERE vendor LIKE 'CH%' You could select columns and build the string in your application ? The query does work and I am getting results from the database. There are values for all 'partnum' in the database, however, the query results include blank fields here and there in between the returned records. Why am I receiving blank fields for 'gmrim' This absolutely defies logic Because one of your fields is probably NULL, and NULL || anything stays NULL. You have probably been misled to believe they are "blanks" because they don't display as "NULL" but as "". I set psql to display NULL as NULL. If these columns can, must, or should not contain NULLs depends on your application... it's for you to chose. Use COALESCE, add NOT NULL constraints, grab the columns and build the string in your application, you chose. --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 269.8.1/822 - Release Date: 5/28/2007 11:40 AM ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] odbc with encrypted ssl key?
Hi! You may try https://projects.commandprompt.com/public/odbcng/. This PostgreSQL ODBC driver's connection string can contain parameters you need: SSL_CERTIFICATE=[string] - path to SSL certificate file SSL_PRIVATE_KEY=[string] - your SSL private key SSL_PASSPHRASE=[string] - your SSL password phrase You can either use these parameters in connection string or configure DSN. Andrei. Andreas wrote: Hi, is there a way to have MS-Access use ODBC and still use a passphrase encrypted private-key? Right now ODBC works with unencrypted key. For security reasons I'd rather have my private key stored encrypted. I suppose to do this Access had to tell the odbc driver the passphrase so that it can decrypt the key. Until now I just found 2 connection string parameters "ssl" and "sslmode" but nothing resembling ssl-passphrase. Regards A. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Error installing postgresql-8.2.4 on windows 2003 server
Hkrenske wrote: I have been successful in installing to Windows 2000 but when installing the binary installation “postgresql-8.2.msi” as a service, I receive the error The program "postgres" is needed by initdb but was not found in the same directory as "C:/Program Files/PostgreSQL/8.2/bin/initdb". Check your installation. I have checked and found both the postgres.exe and initdb.exe files in the directory "C:/Program Files/PostgreSQL/8.2/bin/initdb". Does anyone have any ideas what the problem could be? Hugh Try to run initdb under non-privileged user. You can use runas /user:username "initdb -D ..." Andrei. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Hi all! Everyone who use PostgreSQL server on Windows knows - it would be nice to have some tray management and monitoring tool for PostgreSQL server which is running as NT Service (for example - MS SQL already have such tool). I have created a new project on pgfoundry - http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can we improve this tool and what features whould be helpful. Thanks, Andrei. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Tony Caduto wrote: Andrei Kovalevski wrote: Hi all! Everyone who use PostgreSQL server on Windows knows - it would be nice to have some tray management and monitoring tool for PostgreSQL server which is running as NT Service (for example - MS SQL already have such tool). I have created a new project on pgfoundry - http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can we improve this tool and what features whould be helpful. Thanks, Andrei. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Have you done any development yet? Yes, you can download and try it. Now it's a single pgtray.exe application. I'm going to make an msi installer and add "Autostart" option to the menu. I can do something in Delphi in a matter of hours and would be able to donate the code as a BSD license. I have done a similar tray application for Firebird and it would be just a matter of changing the service it monitors. We could also do a control panel applet. Oh, very interesting. I'm not sure it's really required - but can you provide some links about this? Andrei. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG Admin
Bob Pawley wrote: Can anyone tell me why a table developed through the PG Admin interface isn't found by SQL when accessing it through the SQL interface?? Bob Pawley 1) Are you sure you are connecting to the same database? 2) What kind of SQL interface you are using? Andrei. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fastest way to import only ONE column into a table? (COPY doesn't work)
Phoenix Kiula wrote:
On 16/08/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
On 16/08/07, Rodrigo De León <[EMAIL PROTECTED]> wrote:
On Aug 15, 11:46 pm, [EMAIL PROTECTED] ("Phoenix Kiula") wrote:
Appreciate any tips, because it would
be nasty to have to do this with millions of UPDATE statements!
- Create an interim table
- COPY the data into it
- Do an UPDATE ... FROM ...
Thanks! I thought about it and then gave up because SQL trumped me up.
Could you please suggest what the query should look like?
Based on this:
http://www.postgresql.org/docs/8.1/static/sql-update.html
I tried this:
UPDATE
t1 SET title = title FROM t2
WHERE
t1.id = t2.id;
UPDATE T1
SET T1.title = T2.title
FROM T2
WHERE T1.id = T2.id
AND T1.title IS NULL;
or
UPDATE T1
SET title = ( SELECT title
FROM T2
WHERE T2.id = T1.id )
WHERE T1.title IS NULL;
Thanks much RIchard, but neither of those work. For me table t1 has
over 6 million rows, and table t2 has about 600,000. In both of the
queries above I suppose it is going through each and every row of
table t1 and taking its own sweet time. I've dropped all indexes on
t1, but the query has still been running for over 45 minutes as I
write! Any other suggestions?
I'm not sure would it be faster - but you can try to create a function
which will create new empty table, then fill it with the result of
SELECT query. Something like this:
CREATE OR REPLACE FUNCTION add_column () RETURNS INTEGER AS $$
DECLARE
r RECORD;
BEGIN
CREATE TABLE new_table (id integer, value varchar);
FOR r IN select t1.id, t2.title value from t1 left outer join t2 on
(t1.id = t2.id) LOOP
INSERT INTO new_table VALUES(r.id, r.title);
END LOOP;
return 0;
end
$$ LANGUAGE plpgsql;
Try this function and if its' time would be acceptable - you'll need to
drop existing table and rename newly created one.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installation problems
Hi! [EMAIL PROTECTED] wrote: Hi all. I post it again, not sure to have posted it correctly the last time. My problem is about installing PostgreSQL 8.2.3 on WIndows XP Mediacenter. At the end of the installation, I get the following error: "unable to start the service. Administrator right required". Did you try to start the service manually from Start -> Administrative Tools -> Services -> PostgreSQL Database Server 8.2? Obviously I'm Administrator on my machine (I've cecked it). My question is. "Exists a particolar version of PostgreSQL to download for Windows XP Mediacenter?" Any Idea is appreciated. Thanks in advance. Luca. -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Viaggi, voli, soggiorni...cattura l'offerta e parti con Mondolastminute Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6850&d=20070820 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Windows vista
Hi! PostgreSQL win32 port works pretty well on Vista. You can find latest 8.2 binaries there: http://www.postgresql.org/ftp/binary/v8.2.5/win32/ Use installer from postgresql-8.2.5-1.zip <http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.2.5%2Fwin32%2Fpostgresql-8.2.5-1.zip> - you'll have less problems with permissions. [EMAIL PROTECTED] wrote: Where do I get the link needed to install postgres on windows vista and do I install it. Please help I need it for a school project Sent from my Verizon Wireless BlackBerry ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Andrei ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] number errors
Hello, If you use ODBC - you should devide error from ODBC driver and errors from PostgreSQL, ODBC driver return it's own error codes, and composes error Description depending on Error Code and Text from PostgreSQL server. So you should have numbers: 1) ODBC error code - described in MSDN; 2) Native PostgreSQL error code - described in PostgreSQL manual; 3) Error description - composed by ODBC driver, based on description and error code, returned from server. João Paulo Zavanela wrote: Hi all, When my application returns errors from database, some numbers errors is equals. Why number errors is equals? odbc driver or postgresql return this? It's run in Windows. Thanks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] odbcng
Hello,
This query works for me on Access 2003. Which versions of Access and
ODBCng you have?
We can communicate via [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>. I'll try to help you with any
problems.
Sam Mason wrote:
On Tue, Nov 06, 2007 at 05:48:12PM -0300, Alvaro Herrera wrote:
FYI there's another Postgres ODBC driver that is said to have better
performance.
https://projects.commandprompt.com/public/odbcng
(Yes, my company maintains it)
Are there any known issues when calling it from VB? I've got a VB (MS
Access) client that uses PG as its backend and it seems to die horribly
when doing any sort of query that returns a text column. This happens
through either DAO or ADO; though DAO gives the error "the size of a
field is too long", ADO just segfaults.
For example, the following code doesn't work for me. Looks like the
sort of thing that that should get lots of test coverage so maybe it's
something on my system.
Public Sub test()
Dim con As ADODB.Connection, rs As ADODB.Recordset
Set con = New ADODB.Connection
con.Open "DSN=badgerstudy"
Set rs = con.Execute("SELECT 1, 'foo'::TEXT, 'bar'")
While Not rs.EOF
rs.MoveNext
Wend
End Sub
Thanks,
Sam
---(end of broadcast)---
TIP 6: explain analyze is your friend
Thanks,
Andrei.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Npsql is much faster than ODBC ?
Rainer Bauer wrote: Alvaro Herrera wrote: Rainer Bauer wrote: "Andrej Ricnik-Bay" wrote: On Nov 7, 2007 2:40 PM, Rainer Bauer <[EMAIL PROTECTED]> wrote: That's nice to hear. But I respect licences as they are and the ODBCng driver is licenced under the GPL. That doesn't mean that you're not allowed to use it with commercial applications; it just means that you need to be happy to provide the source for it on request. Which is exactly the reason why the LGPL licence was created. So that any software can link against a library without the restrictions of the GPL. Keep in mind, though, that the ODBC driver is not linked to your app. It is only loaded on demand at run time, and can be replaced by any other ODBC driver. So AFAIU your application is "shielded" from GPL. IANAL of course. Neither am I. However, the GPL FAQ has an entry specially for this case: <http://www.gnu.org/licenses/gpl-faq.html#NFUseGPLPlugins> "If the program dynamically links plug-ins, and they make function calls to each other and share data structures, we believe they form a single program, which must be treated as an extension of both the main program and the plug-ins. In order to use the GPL-covered plug-ins, the main program must be released under the GPL or a GPL-compatible free software license, and that the terms of the GPL must be followed when the main program is distributed for use with these plug-ins." ODBC drivers are loaded by ODBC driver manager - which is also dinamically linked library. Application calls functions from Driver Manager, and then manager goes farther to the driver's level of abstraction. Driver has no information about the parent application, and can't call any functions from it. Driver is not a plug-in, and application doesn't have to worry about its existance. The way I read this section is that linking to a GPL ODBC driver would imply that I have to release my program under a GPL (compatible) licence. This was one of the reasons why I added Postgres support to my program instead of MySQL [1]. They altered the licence for their drivers from LGPL to GPL so that you have to purchase a commercial licence. Rainer [1] In the meantime I am of course glad that I made this decision. I have not only learned a lot more about databases, but especially that Postgres is superior to MySQL ;-) Thanks, Andrei. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Windows x64 Port
Hello, Magnus Hagander wrote: Willem Buitendyk wrote: Is there any plan to port Postgresql to windows x64? I can currently run Postgresql as 32 bit inside Vista 64 - would I see better performance if Postgresql was running under 64 bit. My biggest concern is memory - at 32 bit is not Postgresql limited to 4GB in windows? It's something we hope will be worked on for 8.4, but there are no firm plans. It's limited to 2Gb, actually, but *per process*. Since each backend is it's own process, you can use way more than 2Gb RAM on a 64-bit system. You can't use it for shared memory, but you can use it for local backend memory (work_mem). But you'll need a lot of backends to do it, and you will see other pieces of performance get worse with loads of backend. Oh, and your RAM will still be used for disk cache, since that's managed by the kernel. I'm wondering - what kind of problems do you expect with such port? By the way, are there any benchmark results to compare 32 and 64 bit version on Linux? Thanks, Andrei. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql 8.3rc1 on vista
Hello, The first thing which should be checked on Vista - correct file access permissions. Did you create postgres.conf yourself? Niederland wrote: This seems to be an installer bug for VISTA If I run postgres via the command prompt with: postgres -D ../data then the postgres.conf file is loaded as confirmed with a show all in psql If I start postgres via the service which is installed with installer the postgres.conf file values are not loaded. But I confirmed that the service is reading the postgres.conf file. Just to test I deleted the file, and the service would not start. Any suggestions? Thanks, Roger ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] (un)grouping question
May be this is what you need: select test.uid, coalesce(t.somevalue + a.max + t.uid, test.somevalue) from test left outer join (select * from test where (uid, somevalue) not in (select min(uid), somevalue from test group by somevalue) ) t on (test.uid = t.uid), (select max(somevalue) from test) a Rhys Stewart wrote: ok, let me clarify, dont want to remove them just want them changed but need to keep the uid. However, I would like just one somevalue to remain the same. so for example, uids, 2,4 and 8 have somevalue 44, after i would like 2 to remain 44 but uids 4 and 8 would be changed. 2008/1/21, Jeff Davis <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>: On Mon, 2008-01-21 at 12:36 -0500, Rhys Stewart wrote: > Hi list, > > have the following table > > uid|somevalue > > 1|11 > 2|44 > 3|31 > 4|44 > 5|71 > 6|33 > 7|33 > 8|44 > 9|14 > > would like to remove the duplicate values in the column somevalue. > doing this by just adding a random number is perfectly fine, however > i want to retain at least one of the original values of somevalue. Any > ideas how to do this in in a query? Would something like this help? SELECT MIN(uid), somevalue FROM mytable GROUP BY somevalue; Also consider just doing: SELECT DISTINCT somevalue FROM mytable; ...if you don't need uid in the result set. Regards, Jeff Davis -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] (un)grouping question
Jeff Davis wrote: On Mon, 2008-01-21 at 14:25 -0500, Rhys Stewart wrote: ok, let me clarify, dont want to remove them just want them changed but need to keep the uid. However, I would like just one somevalue to remain the same. so for example, uids, 2,4 and 8 have somevalue 44, after i would like 2 to remain 44 but uids 4 and 8 would be changed. Can you explain why you're trying to do this? It's a very unusual requirement. That being said, the query would look something like this: (SELECT MIN(uid) AS uid, somevalue FROM mytable GROUP BY somevalue) UNION (SELECT uid, somevalue + random() AS somevalue FROM mytable WHERE uid NOT IN (SELECT MIN(uid) FROM mytable GROUP by somevalue) Disclaimer: I haven't actually tested this query, but it looks about right. How can you garantee that "somevalue + random()" won't duplicate other unique values in this column? ;) Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Newbee to databases (ODBC)
Hello,
You can use SQLGetData(...) function:
//Setup selectstatement
strcpy((char*) SQLStmt, "Select no_char, blobdata from tab1");
//Execute the statement
rc = SQLExecDirect(dbConnection.StmtHandle, SQLStmt, SQL_NTS);
//Define what to read out from the selection set
rc =SQLBindCol(StmtHandle, 1, SQL_C_LONG, (SQLPOINTER) &no, sizeOf(no),
NULL);
rc =SQLBindCol(StmtHandle, 2 SQL_C_BINARY, (SQLPOINTER) blob, no, NULL);
// get the values
while(SQLFetch(StmtHandle) != SQL_NO_DATA)
{
void* blob = malloc(no);
if (blob)
{
rc = SQLGetData(StmtHandle, 2, SQL_V_BINARY, blob, no, &no);
...
...
free (blob);
}
}
Malm Paul wrote:
Hi all,
I'm sorry to bother you with this question, I know it is a C++ ODBC
question. But I'm a bit desperate.
Perhaps I'll be lucky.
I have a table with 2 columns no_char and blobdata were no_char is
number of characters in blobdata. I would like to handle each row from
the result set.
I have got this far:
//Setup selectstatement
strcpy((char*) SQLStmt, "Select no_char, blobdata from tab1");
//Execute the statement
rc = SQLExecDirect(dbConnection.StmtHandle, SQLStmt, SQL_NTS);
//Define what to read out from the selection set
rc =SQLBindCol(StmtHandle, 1 SQL_C_LONG, (SQLPOINTER) &no, sizeOf(no),
NULL);
rc =SQLBindCol(StmtHandle, 2 SQL_C_BINARY, (SQLPOINTER) blob, no, NULL);
// get the values
while(SQLFetch(StmtHandle) != SQL_NO_DATA)
{
...
...
}
But this will not work since I'm using "no" in the second statement to
define the blob size, and no is not set yet.
I could solve it by using 2 different select statemen (once for the
size and one for the blob) but that seems to be a bad solution.
Sorry again!
/Paul
--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] errors on COMMIT and transaction states
Hello all, I've got a question about the state in which a session/transaction finds itself in case a COMMIT statement fails. I hope this is a good mailing list to ask it on. Is it true that a failed COMMIT behaves just like a ROLLBACK statement would have if it was issues in its stead? In other words, is it true that the transaction is rolled back (as it is on every error) and the session will accept new statements without the need for the client to issue a ROLLBACK? And to extend the question a bit: Is is true that, if a client uses the "simple query" flavor of the pgwire protocol and sends multiple SQL commands in a single query string looking like "BEGIN; ; ;...;COMMIT" then, when the result(s) come and there was an error somewhere, the session can be in either of two states: - in an error state if the error was encountered by one of stmt1, stmt2, etc. Meaning that future statements sent on future query strings will be rejected with "ERROR: current transaction is aborted, commands ignored until end of transaction block" and the client needs to send a ROLLBACK to get itself out of this sticky situation? - in a regular state, accepting new statements, if the error was encountered by COMMIT. If everything I've said before is true, is there any guidance / best practices that developers and/or client drivers should implement for dealing with this "should I send a ROLLBACK so I can continue to use my connection or not?" question when receiving an error after sending a multi-statement query string that contains a COMMIT in it? Thank you very much, - Andrei
Re: [GENERAL] Connect to postgres from a dynamic IP
Hello, Jorge Godoy wrote: Em Monday 03 March 2008 08:08:36 Raymond O'Donnell escreveu: On 03/03/2008 11:01, dfx wrote: The question il: Is there a method to avoid to insert the addesses of the clients in the pg_hba.conf and to allow connections from internet with security assured only by username and password? Yes, that's what people have been explaining: you insert a line something like: host [database] [user] 0.0.0.0/0 md5 But make it "hostssl" instead of "host", to require some cryptography in the channel used, specially to authenticate the connection. Opening your access to everyone without crypto sounds like something you don't want to do. Specially if users can change their own passwords... Does anybody ever measured performance slowdown for SSL connections? -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Re: [GENERAL] [ODBC] Error in Adding All Table
Hello, What PostgreSQL server and ODBC driver version do you use? salman Sheikh wrote: Hi freinds, i wanted to add my all tables once in MFC application,normally we add it one by one. If i add all table by pressing control and click on all table ,i can add them,but by debugging it shows me always errors. ERROR: column reference "ctid" is ambiguous; Error while executing the query i need also suggestion,if it is better to add all table once or one by one though dialog Box. I am using Visual C++ 2005. thanks Sheikh Pt! Schon vom neuen WEB.DE MultiMessenger geh&oum l;rt? Der kann`s mit allen: *http://www.produkte.web.de/messenger/?did=3123* -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Re: [GENERAL] How to design a "customer" TABLE which hold credit card infos and other payments?
Hello, Scott Marlowe wrote: On Thu, Nov 6, 2008 at 2:43 PM, Michelle Konzack <[EMAIL PROTECTED]> wrote: * Do not Cc: me, because I READ THIS LIST, if I write here * *Keine Cc: am mich, ich LESE DIESE LISTE wenn ich hier schreibe* Sorry, it's how this list works. If you don't want that, there are some options for majordomo you can set to alleviate the issue. I'm not changing how I reply to the list just for you. Hello, I am coding a new OnlineStore (the existing ones fit not my needs, are to complicate to use or simply closed source and too expensive e.g. InterShop) with an integrated powerful ledger. So now it comes to infos about Credit Cards, PayPal and friends... If you are storing credit card data then you must follow the PCI standards for doing so. Look them up on the web and get a copy. Failure to follow their security guidelines will result in you not being allowed to process or handle credit cards. That said, the best way to store them is to not store them. If you still have to, then use some kind of encryption using the user's password as part of the key, and don't store the user's password, only an md5 of it. Also, store the password on one machine, encrypted, do the encryption decryption on another machine Try to avoid storing any card and card holder info, and you definitely shouldn't keep in DB the whole data required to authorize transaction. Just take in mind how dangerous this info could be in case of security leak. -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, ODBCng - http://www.commandprompt.com/
Re: [GENERAL] Encoding using the Frontend/Backend Protocol TCP/IP
Hi could it be that you have errors in your UTF8 string? For example you might use UTF16 encoding, it can explain why some characters force errors but others are not. Can you post here the string and its' encoded version? Raimon Fernandez wrote: Hello, I'm trying to send some strings that have chars outside from standar ascii, like çñàèó Once I'm connected, the client and server both uses UT8Encoding. And I'm sending all the strings encoded in UTF8. At least the received ones are working, as I get the text exactly as it is, with special chars. But when I'm trying to update a row using some of them, I'm getting an error: ERROR 08P01 Invalid string in message pqformat.c 691 pq_getmstring Invalid Front End message type 0 postgres.c 408 socketbackend you have been disconected How should I encode thanks, regards, r. -- 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] Encoding using the Frontend/Backend Protocol TCP/IP
Hi, the string is ok, but the problem is inside the message. The length of the message is incorrect: your message: 5100*46*557064617465207472616E73616374696F6E7320736574206465736372697074696F6E3D27546573742056616C75657364C387272077686572652069643D313133 it should be: 5100*45*557064617465207472616E73616374696F6E7320736574206465736372697074696F6E3D27546573742056616C75657364C387272077686572652069643D313133 Raimon Fernandez wrote: On 19/11/2009, at 18:13, Raimon Fernandez wrote: On 19/11/2009, at 17:27, Kovalevski Andrei wrote: Hi could it be that you have errors in your UTF8 string? For example you might use UTF16 encoding, it can explain why some characters force errors but others are not. It only happens with values like àéïçñ I think UTF8 can handle this ... yes, It can handle it ... if I send the decoding by hand in a very simple update, it works, so there's something with UTF8 conversion that dosn't work ... for example, instead of sending Ç i send their equivalent in UTF8 &HC3+&H87 and it works ... thanks, regards,
[GENERAL] Timestamp with time zone 'negative' problem
Hello, I have a DB in which items which are 'always valid' have a from_date of 19000101 00+1 (Europe/Berlin) When i try to restore the same DB to (Europe/Bucharest), instead of 19000101 00+2, the timestamp becomes "1900-01-01 00:44:24+01:44:24" which is ... strange. My software then complains (...correctly) that 01:44:24 is not a valid timezone. Am i doing something wrong, or should i report this to pg-bugs? Is there any way i can avoid this issue other than setting Timezone for the DB to (Europe/Berlin)? I am considering UPDATE on the rows to 1901 or some such... but that's a very gimmicky solution. Thank you, Andrei Prodan -- Awinta Consulting S.R.L. Canaluluistr. 44 505600 Sacele Brasov, Romania Tel. +40368 / 441024 Fax. +40368 / 441024 -- -- 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] Mammoth replicator
Hello Martin, You can use this mailing list https://lists.commandprompt.com/mailman/listinfo/replicator-general to ask replicator-related questions. And there is the wiki for Mammoth Replicator - https://projects.commandprompt.com/public/replicator. Martín Marqués wrote: I was working on a replication system (open source) for a DB we are using and I was going to go for Slony-I for replication and pg_pool2 for load balancing. Just yeasterday I found out the Mammoth replicator was released as Open Source, and AFAICS it looks more suitable for our needs then Slony-I (we are going to replicate the whole DB). I'm I wrong on this? And finally a question related with the instalation: are there debian binaries to install replicator? TIA -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, ODBCng - http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] grant all privileges to all tables in a database
I'm a refugee from MySQL due to license restrictions. With MySQL, i was used to do "GRANT ALL PRIVILEGES ON dbname.* TO username" to allow a certain user to do anything within a given database. This is useful when using applications that run on a SQL backend, e.g. a blog or a logging server or something like that - one just creates a dedicated database and lets the application rule supreme. On PostgreSQL, i lost about half a day trying to figure it out. I'm posting this message to help others in my situation. I googled for an answer, but everything that i've found was unhelpful. Hopefully this mailing list is indexed by Google. So, you have a database named dbname and a user named username. You want to give the user all privileges on that particular database. On MySQL, it's enough to do this: GRANT ALL PRIVILEGES ON dbname.* TO username [IDENTIFIED BY 'password']; On PostgreSQL, you have to give it privileges not only to the database, but to all components within (tables, sequences and whatnot). The following three commands will grant those privileges, first to the database, then to the tables, then to the sequences. echo "GRANT ALL ON DATABASE dbname TO username;" | psql -d dbname psql -At -d dbname -c "SELECT 'GRANT ALL ON '||tablename||' TO username;' FROM pg_tables WHERE schemaname='public';" | psql -d dbname psql -At -d dbname -c "SELECT 'GRANT ALL ON '||c.relname||' TO username;' FROM pg_class c JOIN pg_namespace n ON (n.oid=c.relnamespace) WHERE c.relkind='S' AND n.nspname='public';" | psql -d dbname It seems to work fine on pgsql version 8. Of course, after creating new tables and stuff, you may have to re-run the last two commands. That is not necessary on MySQL. Thanks to AndrewSN who helped me on IRC. -- Florin Andrei http://florin.myip.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] grant all privileges to all tables in a database
On Mon, 2005-04-11 at 03:28 +0200, Daniel Verite wrote: > Florin Andrei wrote: > > > On MySQL, it's enough to do this: > > > > GRANT ALL PRIVILEGES ON dbname.* TO username [IDENTIFIED BY 'password']; > > > > On PostgreSQL, you have to give it privileges not only to the database, > > but to all components within (tables, sequences and whatnot). The > > following three commands will grant those privileges, first to the > > database, then to the tables, then to the sequences. > > In this case, why not let 'username' create the database and all its objects > so > that it will have all privileges on them afterwards without any specific GRANT > required? Those are not system accounts, just DB accounts. -- Florin Andrei http://florin.myip.org/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Intervals
I had the same problem and wrote a small function create function hours(timestamp without time zone, timestamp without time zone) RETURNS integer as $$select cast( (cast($2 as date) - cast($1 as date)) * 24 + extract(hour from cast($2 as time) - cast($1 as time)) as integer)$$ language SQL IMMUTABLE; Andrei Jake Stride wrote: Is there a way to convert in interval into hours? I have a table that records the amount of time worked by a person and want to sum up all the hours, however with the column being an interval once you reach more than 24 hours it turns that into a day. This is not what I want so instead of outputting 1day 2:00:00 I would want to output 26:00:00 is this possible? Thanks Jake ---(end of broadcast)--- TIP 8: explain analyze is your friend -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Collation problem
Hi all,
I have a table TranslationDictionary in a database initalized with
lc_collation and lc_type de-de .
In the table is the value 'Straße' and I can't insert 'Strasse', but
when I try to create a foreign key on this table, the value 'Strasse' in
the foreign table is unmatched.
Is this a bug, or have I done something wrong?
\d TranslationDictionary
Table "translationdictionary"
Column | Type | Modifiers
++---
ckeydictionary | character(250) | not null
cvalue | character(250) |
Indexes:
"translationdictionary_pkey" PRIMARY KEY, btree (ckeydictionary)
insert into tkp.TranslationDictionary (cKeyDictionary, cValue) values
('Strasse', 'Strasse');
ERROR: duplicate key violates unique constraint
"translationdictionary_pkey"
select * from tkp.TranslationDictionary where cKeyDictionary = 'Strasse';
ckeydictionary | cvalue
+
(0 rows)
Andrei
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] windows 1252 encoding
Is there a reason that the windows code page 1252 is not supported? The encoding Latin1 would be the most appropriate, but 1252 is a superset of ISO8859-1 and supports more characters, and when I want to write them through odbc I get an error. psqlodbc 8 converts everything to utf-8 and then sends it to the server. I have a database with Latin1 encoding, the windows client code page is 1252 and for some characters I get could not convert UTF-8 character 0x00e2 to ISO8859-1 A solution would be to use the UNICODE encoding in the database, but I have read that it's not safe on windows. So, any ideas? Andrei -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.9 - Release Date: 5/12/2005 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] windows 1252 encoding
Magnus Hagander wrote: Is there a reason that the windows code page 1252 is not supported? The encoding Latin1 would be the most appropriate, but 1252 is a superset of ISO8859-1 and supports more characters, and when I want to write them through odbc I get an error. psqlodbc 8 converts everything to utf-8 and then sends it to the server. I have a database with Latin1 encoding, the windows client code page is 1252 and for some characters I get could not convert UTF-8 character 0x00e2 to ISO8859-1 A solution would be to use the UNICODE encoding in the database, but I have read that it's not safe on windows. So, any ideas? This sounds like your client encoding is set to UTF-8. Try setting it to LATIN1 as well (\encoding in psql). thanks for the reply The ODBC (8.0.1.1) sets the encoding to utf8, and converts everything back and forth. The problem still remains, how are the extra characters in 1252 treated by the server with LATIN1 encoding? The error message is a little missleading, it's a character that is represented on 3 bytes, the first one is 0xe2. Andrei -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.9 - Release Date: 5/12/2005 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SQLConnect failure
[EMAIL PROTECTED] wrote: Original Message Subject: Re: [GENERAL] SQLConnect failure From: Bill Moran <[EMAIL PROTECTED]> Date: Mon, April 02, 2007 2:54 pm To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org In response to [EMAIL PROTECTED]: We have code that has been using MSDE/SQL Server successfully for years, and are adding Postgres support. Doing a SQLConnect to connect to a local Postgres server works fine, but if we try to connect to a remote system, the SQLConnect fails, and we get an error code that seems to indicate "The value specified for the argument UserName or the value specified for the argument Authentication violated restrictions defined by the data source.". We can connect via pgadmin to the remote system, so we believe all the little .conf files should be correct, but can't get in programmatically. Any pointers on where to look? The logs on the PostgreSQL server would be a good place to start. This sounds suspiciously like a pg_hba.conf misconfig. You might want to verify its correctness. Thanks guys. I can connect to the remote server via pgadmin on a different machine, so I'm pretty sure that the .conf files are correct (that took awhile, but there are very good diagnostic messages when they are wrong). When I set the hba, the encryption is set to MD5 - does that need to be set somewhere on the client side? What version of the PostgreSQL ODBC driver you are using? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] query from a list of ids
You can try this one. SELECT table2.* FROM (SELECT string_to_array(ids, ', ') FROM table1 WHERE name = 'Peter') AS a(a), (SELECT generate_series(1,array_upper(string_to_array(ids, ', '),1)+1,1)FROM table1 WHERE name = 'Peter') c(n), table2 WHERE table2.id = a[c.n] finecur wrote: Hi, Here is my first table: Table1 name| ids - Peter| 2, 3, 4, 5 Jack| 100, 34, 3 Both name and ids are in text format. Here is my second table Table2 id | Flag | Title - 2 | Red| good 3 | Blue | poor 4 | Green| middle id is in integer (serial) format. I would like to list all the rows in table 2 where the id is in the ids field of peter. So I did select * from tables where id in (select ids from table1 where name='Peter') It did not work. How can I do the query? Thanks, ff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] C functions under windows
Islam Hegazy wrote: Hi all I have postgresql server installed on a windows machine and I want to retrieve data using C functions. I followed the steps in the documentation but it didn't work for windows. I created a .dll projects for my functions but postgres.h calls .h files that I can't find on the windows machine like . I tested my functions on another server installed on a linix machine and it worked correctly. So the problem appears to be in the include files under windows. Any idea how to solve this problem... Regards Islam Hegazy If you use MSVC - try to setup 'Additional include directories' for your project. It should point to the PostgreSQL 'include' folder. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dns less connection
Hi! First of all, be sure you have PostgreSQL ODBC driver installed. To do this - follow: start ->control panel -> administrative tools -> data sources (ODBC) -> drivers If you see in a list "PostgreSQL ANSI" or "PostgreSQL Unicode" - this means driver is installed, otherwise - you should install one of the PostgreSQL ODBC drivers: http://www.postgresql.org/ftp/odbc http://projects.commandprompt.com/projects/public/odbcng If you already have installed driver - post here your ConnectionString, so we see it and help. marcelo Cortez wrote: Andreas ,Magnus I do where you say me but... ' ''IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified''') i'm follow your instrutions and replace parts of connectString but don't work . Later a try with debugging options. best regards MDC --- Andreas <[EMAIL PROTECTED]> escribió: http://archives.postgresql.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] ERROR: did not find '}' at end of input node (again)
This happend again, but now, postgresql tells me where it happens:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: did not find '}' at end of
input node
pg_dump: The command was: select (select usename from pg_user where
usesysid = datdba) as dba, pg_encoding_to_char(encoding) as encoding,
datpath from pg_database where datname = 'dc'
pg_dumpall: pg_dump failed on database "dc", exiting
template1=# select usename from pg_user;
ERROR: did not find '}' at end of input node
\d pg_user
* QUERY **
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname ~ '^pg_user$'
ORDER BY 2, 3;
**
* QUERY **
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_catalog.pg_class WHERE oid = '16683'
**
* QUERY **
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16683' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**
* QUERY **
SELECT pg_catalog.pg_get_viewdef('16683'::pg_catalog.oid, true)
**
ERROR: did not find '}' at end of input node
I think (hope) I can save the data by copying the data on another computer
and start postgresql there and then dump it (just like I did last time).
The problem is, why does this happen and how can I prevent it ? Maybe this
is caused by an unproper shutdown of postgresql ?
SELECT VERSION();
version
--
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031022 (Gentoo
Linux 3.3.2-r2, propolice)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] ERROR: did not find '}' at end of input node (again)
I've recompiled without the patch and all is fine now. I'm sorry for waisting your time... On Thu, 11 Dec 2003, Tom Lane wrote: > Andrei Ivanov <[EMAIL PROTECTED]> writes: > > It's an almost clean build (it has > > http://gppl.terminal.ru/hier-Pg7.4-0.3.tar.gz in it)... > > That's your problem, then; it makes incompatible changes in stored > rules. The patch should have included a catversion.h change to force > you to initdb after applying it. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] dump/restore problem
You will find more info attached. What about the invalid UNICODE data ? How can I fix it ? In the attachment there is the line that gives the error... Thanks. On Tue, 13 Jan 2004, Tom Lane wrote: > Andrei Ivanov <[EMAIL PROTECTED]> writes: > > I have a dump created with pg_dump ver 7.4.1 from a postgres 7.4 database, > > with encoding SQL_ASCII. > > I'm trying to import this dump in a 7.4.1 postgres database with encoding > > UNICODE, but I'm getting some errors: > > > psql:dump.sql:1277: ERROR: invalid memory alloc request size > > 1073741824 > > psql:dump.sql:3490: ERROR: invalid byte sequence for encoding > > "UNICODE": 0xce20 > > The "invalid byte sequence" problem occurs because you are trying to > load data that isn't valid UNICODE. You need to fix the data. > > I'm more interested in the "invalid memory alloc request" messages, > myself. Could you show us the sections of the dump file that trigger > those errors? > > regards, tom lane > d.sql Description: Binary data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] dump/restore problem
I've set the encoding to LATIN2 and everything worked fine... no encoding
errors and no memory allocation failures...
The tables where the memory failures occured are:
CREATE TABLE press_releases (
id serial NOT NULL,
title character varying(255) NOT NULL,
body text NOT NULL,
tdate date DEFAULT ('NOW'::text)::date NOT NULL
);
CREATE TABLE symbols (
id serial NOT NULL,
name character varying(255) NOT NULL,
description text NOT NULL
);
I was previously trying with encoding set to SQL_ASCII, because that was
the encoding of the database from which the data was taken from and where
everything was working fine with php, but I was having troubles with java
displaying the characters wrong... now I'm trying to see if with the data
converted to unicode works.
Thanks.
On Wed, 14 Jan 2004, Tom Lane wrote:
> Andrei Ivanov <[EMAIL PROTECTED]> writes:
> > You will find more info attached.
>
> I could not reproduce the "out of memory" failure using this information
> (I got "invalid byte sequence" instead, on both of those COPY commands).
> What exactly is the declaration of the tables being copied into?
>
> > What about the invalid UNICODE data ? How can I fix it ?
>
> It looks like your data is in a single-byte encoding, perhaps latin1 or
> one of its siblings. To convert to unicode you will first have to
> identify just what encoding you're really using. Then put a "set
> client_encoding = whatever;" command at the top of the dump script (or
> set it in postgresql.conf, if that seems easier) and you should be able
> to import.
>
> regards, tom lane
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] ERROR: nodeRead: did not find '}'
Hello, it seems my postgresql data has somehow become corrupted (by a forced shutdown I think): psql template1 -U shadow Password: ERROR: nodeRead: did not find '}' at end of plan node Welcome to psql 7.3.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=> \d ERROR: current transaction is aborted, queries ignored until end of transaction block template1=> abort; ROLLBACK template1=> \d ERROR: nodeRead: did not find '}' at end of plan node Every command that tries to access a table gives this error... pg_dumpall says: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: nodeRead: did not find '}' at end of plan node pg_dump: The command was: select (select usename from pg_user where usesysid = datdba) as dba, encoding, datpath from pg_database where datname = 'cinema' pg_dumpall: pg_dump failed on cinema, exiting What can I do ? (postgresql 7.3.4 on a gentoo linux box, with psql from the same package) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ERROR: nodeRead: did not find '}' [x2]
On Thu, 20 Nov 2003, Tom Lane wrote:
> Andrei Ivanov <[EMAIL PROTECTED]> writes:
> > select * from pg_rewrite where ev_class = 'pg_user'::regclass gives
> > [ lots of cruft ]
>
> Well, that's interesting, because I get exactly the same data from my
> perfectly-functional 7.3.4 installation.
>
> I think there must be some incompatibility between your data directory
> and your Postgres executable, but it's hard to think what. Have you
> verified that your executable really is 7.3.4 (try "select version()")?
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031022 (Gentoo Linux 3.3.2-r2, propolice)
>
> I suppose it could also be that the problem is not in pg_user after all,
> but some other view. I didn't think pg_get_viewdef() would use any
> other views, but I might be mistaken. Can you successfully do
> pg_get_viewdef on any other views --- pg_tables or pg_indexes for
> example?
>
template1=> select pg_get_viewdef('pg_tables'::regclass);
ERROR: nodeRead: did not find '}' at end of plan node
template1=> select pg_get_viewdef('pg_indexes'::regclass);
ERROR: nodeRead: did not find '}' at end of plan node
template1=>
> Also, how did you get into this state, exactly? I cannot believe that
> it's a matter of a forced shutdown; I think there must be some kind of
> software compatibility issue involved. What system updates have you
> done recently?
>
> regards, tom lane
I really can't think of anything else that could have brought postgresql
in this state... (could on minor update to glibc cause this ? I don't
remember if updated glibc before or after the corruption...)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
