[GENERAL] dblink / Insert several records into remote table

[P. Broennimann]

Hello

I'd like to use dblink to insert records (several) from a local table to a
remote table. Is it possible to do this in one statement? Something like:

insert into remotetable select col1, col2, col3 from localtable

Can someone give me an example or tell me what commands to use? What I have
so far:

l_connect_str = 'hostaddr=192.168.10.21 port=5432 dbname=db1
user=postgres password=test';
perform public.dblink_connect('con1', l_connect_str);

...

perform public.dblink_disconnect('conn1');

P.S: I could pull the data (select) from the remote DB using dblink, but
I prefer to push the data (insert) from the local DB to reduce the
network traffic.

Thanks,
Peter

[GENERAL] Document Management System (DMS)

[P. Broennimann]

Hi there

I am looking for an open-source document management system (DMS) based on
PostgreSQL.

Anyone has experience with such tools?

Thanks  cheers,
Peter

[GENERAL] Characters

[P. Broennimann]

Hi there

I am using PG 9.2 and 'pg_database' shows 'en_US.UTF-8'.

I have a text 'Piqué' but this is shown in my application as 'Piqu?' so I
was wondering where the problem is?

I am using Devart's 'PostgreSQL Data Access Components' to access PG from
FreePascal.

In my FreePascal code I do use AnsiStrings and I cast the database
results/text 'UTF8ToAnsi(...)'

Thanks for a hint!

Cheers,
P.

Re: [GENERAL] Characters

[P. Broennimann]

Hi Adrian

Thanks a lot! After spending the day checking all sorts of things you saved
my day :)

- I just added the unit 'cwstring' and now it works.

Thanks  good day

Cheers,
Peter

--
Peter Broennimann
Untergasse 11 a
CH-8476 Unterstammheim
Switzerland
--


2013/4/10 Adrian Klaver adrian.kla...@gmail.com

 On 04/10/2013 02:53 AM, P. Broennimann wrote:

 Hi there

 I am using PG 9.2 and 'pg_database' shows 'en_US.UTF-8'.

 I have a text 'Piqué' but this is shown in my application as 'Piqu?' so
 I was wondering where the problem is?

 I am using Devart's 'PostgreSQL Data Access Components' to access PG
 from FreePascal.

 In my FreePascal code I do use AnsiStrings and I cast the database
 results/text 'UTF8ToAnsi(...)'

 Thanks for a hint!


 Just guessing, but from FreePascal docs:

 http://www.freepascal.org/**docs-html/rtl/system/**utf8toansi.htmlhttp://www.freepascal.org/docs-html/rtl/system/utf8toansi.html

 
 Description

 Utf8ToAnsi converts an utf8-encode unicode string to an ansistring. It
 converts the string to a widestring and then converts the widestring to an
 ansistring.

 For this function to work, a widestring manager must be installed.
 

 Is there a widestring manager?


 http://www.freepascal.org/**docs-html/rtl/system/**
 setwidestringmanager.htmlhttp://www.freepascal.org/docs-html/rtl/system/setwidestringmanager.html


 Cheers,
 P.




 --
 Adrian Klaver
 adrian.kla...@gmail.com

[GENERAL] Issue installing an extension

[P. Broennimann]

Hi there

I have installed 9.2.2 on a Debian Squeeze VM.

I am trying to install a third party extension.

1) For the extension I did make  make install.
2) I now see the extension files in /usr/share/postgresql/9.2/extension/...
(files .sql and .control)
3) When I issue CREATE EXTENSION ...  I get an error:

SQL Error: ERROR:  could not open extension control file
/usr/share/postgresql/9.1/extension/...control: No such file or
directory

- Seems that CREATE EXTENSION is using ...9.1... in the path. That is
strange as I never installed 9.1

How can I fix that?

Thx  cheers,
Peter

[GENERAL] Installation Issue on Ubuntu under Xen

[P. Broennimann]

Hi there

I have a VM running under XEN XCP. The VM is Ubuntu server 12.04.1/64
headless.

The VM is completely fresh  clean and works fine. Then I install
Postgresql...

   sudo apt-get install python-software-properties

   sudo add-apt-repository ppa:pitti/postgresql
   sudo apt-get update
   sudo apt-get install postgresql-9.2


...what works well. But when I reboot I see/get the following error:


   * Starting load fallback graphics devices  [fail]


Postgresql seems to work fine but anyone knows what is this error all
about? I never saw such a thing under my old Ubuntu 10.04 VM!?

Thanks  cheers,
Peter

[GENERAL] Pg/SQL returning composite type

[P. Broennimann]

Hi there

I have in schema core:

   CREATE OR REPLACE FUNCTION core.f_read
   (
 ...
   )
   RETURNS core.c_result_type AS
   $$
   declare
 c_result core.c_result_type%rowtype;
   begin
 ...
 return c_result;
   end
   ...

   CREATE TYPE core.c_result_type AS (
 a_valuetext,
 a_result_code  integer,
 a_result   text,
 a_time integer
   );

And in schema canu:

   CREATE OR REPLACE FUNCTION canu.f_read
   (
 ...
   )
   RETURNS canu.c_result_type AS
   $$
   declare
 c_result canu.c_result_type%rowtype;
   begin
 select core.f_read(...) into c_result;
 return c_result;
   end
   ...

   CREATE TYPE canu.c_result_type AS (
 a_valuetext,
 a_result_code  integer,
 a_result   text,
 a_time integer
   );

When calling core.f_read() the result is fine. When calling canu.f_read() I
get all values back together/concatenated in the first a_value field!?

What am I doing wrong here? I am using PG 9.1.6 under Ubuntu 10.04/64

Thanks  cheers,
Peter

[GENERAL] Purge Logs from pgagent

[P. Broennimann]

Hi there

I am using pgagent without problems but I have a few questions:

- I see that pgagent does log its acivities in the tables pga_joblog and
pga_jobsteplog. My log gets quiet big.

1) Is it save to empty these tables once a while manually?

2) Is there a settings to tell pgagent to purge these automatically? Or
tell pgagent to not log at all?

Thanks  cheers,
Peter

[GENERAL] NOTIFY/LISTEN in Postgresql

[P. Broennimann]

Hi there

1) Can a Pg/SQL function listen for a notification sent from an external
instance?
I would like my stored function to pause/wait and continue its execution
once an external event (NOTIFY event) occurs.

2) In Pg/SQL I can implement a loop (until something happens) to
pause/wait. This costs CPU time - Is there another solution? Actually I
would need something like a trigger to give my stored function the signal
to continue.

Thx  cheers,
Peter

Re: [GENERAL] NOTIFY/LISTEN in Postgresql

[P. Broennimann]

Thx for the feedback I will take a look.

Here some details. Basically what I'd like to achieve:

  Internet - AppliA - PostgreSQL - AppliB

1) AppliA receives a request from the internet and calls a Pg/SQL function.
2) The Pg/SQL function informs AppliB that there is some work waiting
(NOTIFY).
3) AppliB does the work and posts the result back to PostgreSQL.

... and now here's the problem - The initial Pg/SQL function should give
back the result computed by AppliB - It has to wait somehow and get
informed when the work from AppliB is done.

Thx  cheers,
Peter



2012/10/15 Craig Ringer ring...@ringerc.id.au

 On 10/15/2012 08:54 PM, P. Broennimann wrote:

 Hi there

 1) Can a Pg/SQL function listen for a notification sent from an
 external instance?


 No, it's the other way around. A client can `LISTEN` for a `NOTIFY` sent
 by another client, either directly or via a PL/PgSQL function.

 What you want is a NOTIFY callback or NOTIFY trigger, something that
 invokes a function without any client action when a NOTIFY comes in. No
 such feature exists.

  I would like my stored function to pause/wait and continue its execution
 once an external event (NOTIFY event) occurs.


 Use an advisory lock, they're ideal for that job:

 http://www.postgresql.org/**docs/current/static/explicit-**
 locking.html#ADVISORY-LOCKShttp://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS

  2) In Pg/SQL I can implement a loop (until something happens) to
 pause/wait. This costs CPU time - Is there another solution?


 Depends on what you're waiting for. Details?

 Again, an advisory lock may be a candidate.

 --
 Craig Ringer

[GENERAL] Output of query_to_xml

[P. Broennimann]

Hi there

In my stored function I use:

select query_to_xml('select * from table12', true, true, '') into ...


The result is OK but there is always an empty line:

row xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
 *-- Empty line here*
 vanoaoFG8976SDFRETG/vanoao
  country_codeNL/country_code
 build_date2011-02-28/build_date
  tcodeMFT/tcode
 exterior_color_codeGAB/exterior_color_code
  interior_color_codeTAFP/interior_color_code
/row


Is this supposed to be or is this a cosmetic bug?

Thanks,
P.

[GENERAL] dblink (INSERT ..... RETURNING)

[P. Broennimann]

Hi there

I have a remote table that I access over dblink.
The table has a primary field set to autoincrement.

I need now to insert a record in that remote table and get back the new
primary field (autoincrement sequence).

Since bdlink_exec can not give back rows I tried to use dblink, but I
can not figure out how to format that INSERT... RETURNING query?

Thanks,
P.

Re: [GENERAL] Problem with pgAgent on Ubuntu

[P. Broennimann]

Eureka!

I changed 127.0.0.1 for the PG's assiged IP address 192.168. and now it
works :)

Thx  cheers,
P.



2012/1/16 Tom Lane t...@sss.pgh.pa.us

 P. Broennimann peter.broennim...@gmail.com writes:
  - The problem I am having is that I can not start pgagent. When I try:

  cd /usr/bin
  ./pgagent hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres

  and I get the following errors:

  /usr/bin$ WARNING: Couldn't create the primary connection (attempt 1):
  could not connect to server: Connection refused
  Is the server running on host 127.0.0.1 and accepting
  TCP/IP connections on port 5432?

 Connection refused is a kernel-level error, not Postgres refusing the
 connection.  Either the postmaster is not actually listening on that
 port, or there is a packet filter rejecting the traffic.  I'd bet on the
 latter given what you've said.

regards, tom lane

[GENERAL] Basic PostgreSQL Question

[P. Broennimann]

Hi there

I have installed pgAgent (scheduling) in the default postres database on
my server. I read somewhere that this is where the linux pgAgent expects to
find its tables.

My own application database is called xyz on the same server.
Now I need to create pgAgent jobs from my xyz stored functions.

How can I access the postgres database from my stored functions to
execute INSERT statements...

Thanks  cheers,
P.

[GENERAL] Problem with pgAgent on Ubuntu

[P. Broennimann]

Hi there

My PostgreSQL 9.1 installed on an Ubuntu/64 10.04 machine is running fine.

I installed pgAgent with sudo apt-get install pgagent (that is probably
version 3.0.1).

I executed the .sql script to create the pgagent objects in the database.

sudo su postgres
psql -d postgres
postgres-# \dn
  List of schemas
  Name   |  Owner
-+--
 pgagent | postgres
 public  | postgres
(2 rows)

- Database seems fine!

- The problem I am having is that I can not start pgagent. When I try:

cd /usr/bin
./pgagent hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres

and I get the following errors:

/usr/bin$ WARNING: Couldn't create the primary connection (attempt 1):
could not connect to server: Connection refused
Is the server running on host 127.0.0.1 and accepting
TCP/IP connections on port 5432?
WARNING: Couldn't create the primary connection (attempt 2): could not
connect to server: Connection refused
Is the server running on host 127.0.0.1 and accepting
TCP/IP connections on port 5432?
WARNING: Couldn't create the primary connection (attempt 3): could not
connect to server: Connection refused
Is the server running on host 127.0.0.1 and accepting
TCP/IP connections on port 5432?
WARNING: Couldn't create the primary connection (attempt 4): could not
connect to server: Connection refused
Is the server running on host 127.0.0.1 and accepting
TCP/IP connections on port 5432?

Any idea?

P.S: I also tried to run pgagent with sudo. - The same errors!

Thanks,
P.