Re: [GENERAL] Streaming Replication archive_command is really needed?

[Leonardo M . Ramé]

El 20/07/17 a las 16:57, Andreas Kretschmer escribió:

On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé" <l.r...@griensu.com> 
wrote:

Hi, I wonder if archive_mode=on and archive_command parameters in
postgresql.conf are really needed for streaming replication between two

servers (master-slave).

Regards,


No.

Andreas



So, can I just comment those commands and remove the main/archive directory?

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Streaming Replication archive_command is really needed?

[Leonardo M . Ramé]

Hi, I wonder if archive_mode=on and archive_command parameters in 
postgresql.conf are really needed for streaming replication between two 
servers (master-slave).


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Foreign Data Wrapper for filesystem

[Leonardo M . Ramé]

El 15/02/17 a las 13:27, John McKown escribió:
On Wed, Feb 15, 2017 at 10:20 AM, Tom Lane <t...@sss.pgh.pa.us 
<mailto:t...@sss.pgh.pa.us>>wrote:


"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" <l.r...@griensu.com
<mailto:l.r...@griensu.com>> writes:
> Hi, I'm looking for a FDW that allows listing a directory as a
database
> table allowing me to check if file exists, does anyonke know if
such FDW
> exists?.

Why not use pg_ls_dir()?  An FDW would be mighty awkward to use
for this
purpose, even if one existed.


​It cannot be used for an arbitrary directory:
" Only files within the database cluster directory and the 
log_directory can be accessed. "​

​ref: https://www.postgresql.org/docs/current/static/functions-admin.html​


regards, tom lane


​Personally, I don't know what use such a function would be. It would 
be executed on the _SERVER_, not the client. And that is probably why 
the pg_ls_dir() is restricted. If it weren't it could be a security 
(or privacy) violation.​


--
"Irrigation of the land with seawater desalinated by fusion power is 
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion


Maranatha! <><
John McKown


Well, I need to check the existence of a file from a query/procedure. 
After posting I remembered I could do this with plperlu, I'm installing 
it right now.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877

[GENERAL] Foreign Data Wrapper for filesystem

[Leonardo M . Ramé]

Hi, I'm looking for a FDW that allows listing a directory as a database 
table allowing me to check if file exists, does anyonke know if such FDW 
exists?.




Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Alter view with psql command line

[Leonardo M . Ramé]

El 10/02/17 a las 14:17, Adrian Klaver escribió:

On 02/10/2017 09:09 AM, Leonardo M. Ramé wrote:

Hi, is there a way to alter a view using *psql*?, something like what
\ef does for functions.


In 9.6:



That's why in 9.1 I didn't find that command...


https://www.postgresql.org/docs/9.6/static/app-psql.html

\ev [ view_name [ line_number ] ]



Thanks Adrian.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Alter view with psql command line

[Leonardo M . Ramé]

Hi, is there a way to alter a view using *psql*?, something like what 
\ef does for functions.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [Off Topic] Visualizing grouping sets/cubes

[Leonardo M . Ramé]

Hi, I'm reading about Grouping Sets/Rollup/Cube and I wonder which 
js/html5 library allows displaying *easily* (without having to re-format 
it) the returned data from those functions.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Ajuda com definição

[Leonardo M . Ramé]

El 24/01/17 a las 16:35, Márcio A. Sepp escribió:

Boa tarde,



Tenho um caso onde o campo chave da tabela irá receber dois tipos de
informação: integer de tamanho 5 e integer de tamanho 7.
O problema disso é que se eu criar o campo como sendo integer, lá pelas
tantas corro o risco de dar violação de PK.

As soluções possíveis seriam criar o campo como varchar(7) ou colocar um
segundo campo na chave para identificar a informação.

A considerar:
99,9% dos registros desta tabela são de tamanho 7.

Dutra e demais da lista, qual a forma mais correta de modelar isso?


--
Att.
Márcio A. Sepp



Maybe you can create two Numeric fields, for example:

create table numbers(
  id serial not null,
  number_7 numeric(7, 0),
  number_5 numeric(5, 0),
  primary key(id)
);

Then

insert into numbers(number_7, number_5) values(12345678, 12345); <-- ERROR
insert into numbers(number_7, number_5) values(1234567, 123456); <-- ERROR
insert into numbers(number_7, number_5) values(1234567, 12345); <-- OK

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Add trigger to FDW table

[Leonardo M . Ramé]

Hi, I wonder if I can add a trigger to a Foreign Data Wrapper table. I 
can't create the trigger in the foreign database.



Regards,

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Improving speed of query

[Leonardo M . Ramé]

Hi, I'm using a query to fill a paginated table. The task involves 
filtering, sorting, limit, offset and count of all rows (to determine 
the number of pages).


My current query is this:

select count(*) over() as totalrows,
  case when (d.filepath is not null) then '1' else '0' end as HasDocument,
  e.idtask, e.site, e.pacs, e.studyuid, e.accessionnumber,
  e.patientemail, e.refphysicianemail, e.sent, e.password, e.created,
  e.institutionname, e.patientname, e.studydate, e.studytime,
  e.proceduredescription, e.performingphysician, e.referringphysician,
  e.informantphysician, e.forcesend, e.sentdate, e.md5identifier,
  e.read, e.patientid
from emailtasks e
join sites s on s.identifier = e.site
left join documents_current d on d.idtask=e.idtask
where s.idsite = 34
order by e.idtask desc
limit 10 offset 0;

I've made several indexes, and they really fast. The problem here is the 
window function count(*) to get the total number of rows.


Here's the explain analyze result:

 Limit  (cost=0.84..57.98 rows=10 width=310) (actual 
time=36075.589..36079.371 rows=10 loops=1)
   ->  WindowAgg  (cost=0.84..84302.61 rows=14754 width=310) (actual 
time=36075.581..36079.356 rows=10 loops=1)
 ->  Nested Loop Left Join  (cost=0.84..84118.19 rows=14754 
width=310) (actual time=0.085..30639.311 rows=258839 loops=1)
   ->  Nested Loop  (cost=0.42..39977.25 rows=10170 
width=260) (actual time=0.071..10308.789 rows=146782 loops=1)

 Join Filter: (e.site = s.identifier)
 Rows Removed by Join Filter: 66794
 ->  Index Scan using idx_emailtasks_idtask on 
emailtasks e  (cost=0.42..36772.35 rows=213576 width=260) (actual 
time=0.013..9929.527 rows=213576 loops=1)
 ->  Materialize  (cost=0.00..1.27 rows=1 width=16) 
(actual time=0.000..0.001 rows=1 loops=213576)
   ->  Seq Scan on sites s (cost=0.00..1.26 
rows=1 width=16) (actual time=0.024..0.026 rows=1 loops=1)

 Filter: (idsite = 34)
 Rows Removed by Filter: 20
   ->  Index Scan using idx_documents_current_idtask on 
documents_current d (cost=0.42..4.32 rows=2 width=54) (actual 
time=0.092..0.136 rows=2 loops=146782)

 Index Cond: (idtask = e.idtask)
 Total runtime: 36106.813 ms
(14 rows)

What strategy do you recommend for speeding up this query?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

[Leonardo M . Ramé]

El 02/09/16 a las 18:28, Scott Marlowe escribió:

On Thu, Sep 1, 2016 at 8:48 PM, Patrick B <patrickbake...@gmail.com> wrote:
Why not just subscribe to another cluster on the master, then sub the
slaves to that, then switchover to the new cluster on the master?



Maybe he  doesn't know how to do that, would you care to explain?

Regards,

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Test letter

[Leonardo M . Ramé]

El 15/09/16 a las 11:57, Alex Sviridov escribió:

Hi all,

I have suspicions that my message don't get to pgsql-general mailing list.

Please, someone, answer this message if this get the mailing list.


Best regards, Alex



Arrived ok.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] ERROR: could not read block 4 ...

[Leonardo M . Ramé]

El 15/09/16 a las 11:05, Tom Lane escribió:

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" <l.r...@griensu.com> writes:

Hi, I'm getting this error:
2016-09-15 09:35:24 ART [13984-1] ERROR:  could not read block 4 in file
"base/12210/11965": Error de entrada/salida

That looks like a hardware problem ... might want to budget for a new disk
sometime soon, before failures become more prevalent.


1) Is this caused by a bad block?, can fsck fix it?.

yes, and fsck is unlikely to help, though you could try.


2) Is there a way to re-generate template0?.

If you've never modified template1, you could drop template0 and re-create
it from template1.  Otherwise, pg_dumpall/initdb/reload would seem to be
called for.  A cautious person might want to do the latter anyway in case
there's more problems than just this one.

regards, tom lane


Thanks, indeed, I'm moving the Db to another server.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ERROR: could not read block 4 ...

[Leonardo M . Ramé]

Hi, I'm getting this error:

2016-09-15 09:35:24 ART [13984-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida
2016-09-15 09:35:39 ART [14082-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida
2016-09-15 09:35:54 ART [14170-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida
2016-09-15 09:36:09 ART [20704-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida


To find out which database points to it I use:

mydb=# select oid, datname from pg_database;
  oid  |  datname
---+---
 1 | template1
 12210 | template0 <--- Looks like this is the problematic db
 12215 | postgres
 16384 | mydb

Two questions:

1) Is this caused by a bad block?, can fsck fix it?.
2) Is there a way to re-generate template0?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] londiste re-create leaf node

[Leonardo M . Ramé]

El 09/09/16 a las 10:53, Leonardo M. Ramé escribió:
Hi, I had a londiste3 slave server that no longer works and I want to 
re-create.


I dropped all the pgq and londiste schemas in the leaf node 
manually...now I want to create the node again, but I'm getting this 
error:


londiste3 /home/londiste/replicacion/db_esclavo.ini create-leaf 
node_esclavo "dbname=database host=192.65.214.199" 
--provider="dbname=database host=192.65.214.204"


ProgrammingError: schema "pgq_node" does not exist

This is obvious, the question is how can I re-create that schema.

I tried generating the leaf node again, from the master database, but 
I get this:


2016-09-09 10:58:41,925 9680 INFO plpgsql is installed
2016-09-09 10:58:41,926 9680 INFO Installing pgq
2016-09-09 10:58:41,957 9680 INFO   Reading from 
/usr/local/share/skytools3/pgq.sql
2016-09-09 10:58:45,130 9680 ERROR Job job_repli_ris_master got error 
on connection 'db': could not access file "$libdir/pgq_lowlevel": No 
such file or directory.


This is weird, because master is working without issues.

Regards,


Mmm, built and reinstalled skytools-3.2, but now I'm getting this:

InternalError: could not load library 
"/usr/lib/postgresql/9.5/lib/pgq_lowlevel.so": 
/usr/lib/postgresql/9.5/lib/pgq_lowlevel.so: undefined symbol: oid_hash



--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] londiste re-create leaf node

[Leonardo M . Ramé]

Hi, I had a londiste3 slave server that no longer works and I want to 
re-create.


I dropped all the pgq and londiste schemas in the leaf node 
manually...now I want to create the node again, but I'm getting this error:


londiste3 /home/londiste/replicacion/db_esclavo.ini create-leaf 
node_esclavo "dbname=database host=192.65.214.199" 
--provider="dbname=database host=192.65.214.204"


ProgrammingError: schema "pgq_node" does not exist

This is obvious, the question is how can I re-create that schema.

I tried generating the leaf node again, from the master database, but I 
get this:


2016-09-09 10:58:41,925 9680 INFO plpgsql is installed
2016-09-09 10:58:41,926 9680 INFO Installing pgq
2016-09-09 10:58:41,957 9680 INFO   Reading from 
/usr/local/share/skytools3/pgq.sql
2016-09-09 10:58:45,130 9680 ERROR Job job_repli_ris_master got error on 
connection 'db': could not access file "$libdir/pgq_lowlevel": No such 
file or directory.


This is weird, because master is working without issues.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Londiste3 - Ubuntu 16.04 - Postgresql 9.3

[Leonardo M . Ramé]

El 03/06/16 a las 16:29, Adrian Klaver escribió:

On 06/03/2016 12:08 PM, Leonardo M. Ramé wrote:

Hi again, I'm trying to create the root node using londiste3 from Ubuntu
16.04 connected to a PostgreSql 9.3 db, but I get this:

londiste3 /home/leonardo/replication/pacsio_master.ini create-root
master_node "dbname=test host=127.0.0.1"


2016-06-03 16:03:39,257 27600 INFO plpgsql is installed
2016-06-03 16:03:39,257 27600 INFO Installing pgq
2016-06-03 16:03:39,258 27600 INFO   Reading from
/usr/local/share/skytools3/pgq.sql
2016-06-03 16:03:40,013 27600 ERROR Job job_repli_test_master got error
on connection 'db': could not access file "$libdir/pgq_lowlevel": No
existe el archivo o el directorio.   Query: CREATE OR REPLACE FUNCTION
pgq.insert_event_raw(
queue_n ...
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/skytools/scripting.py",
line 579, in run_func_safely
r = func()
  File "/usr/local/lib/python2.7/dist-packages/skytools/adminscript.py",
line 62, in work
fn(*cmdargs)
  File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py",
line 147, in cmd_create_root
return self.create_node('root', args)
  File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py",
line 198, in create_node
self.install_code(db)
  File "/usr/local/lib/python2.7/dist-packages/londiste/setup.py", line
29, in install_code
CascadeAdmin.install_code(self, db)
  File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py",
line 425, in install_code
skytools.db_install(db.cursor(), objs, self.log)
  File "/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py",
line 531, in db_install
obj.create(curs, log)
  File "/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py",
line 490, in create
curs.execute(stmt)
  File "/usr/local/lib/python2.7/dist-packages/psycopg2/extras.py", line
120, in execute
return super(DictCursor, self).execute(query, vars)
OperationalError: could not access file "$libdir/pgq_lowlevel": No
existe el archivo o el directorio

P.S.: Here there's a suggestion of installing postgresql-9.4-pgq3, but
as I have 9.3 I cannot install the recommended package (it will install
9.4 and start a new cluster).


That would seem to be if you wanted to install the Skytools binaries. 
You are building from source.


What where the steps you took to configure and build the package?

Where there any error/warning messages when building?

My suspicion is the below from the INSTALL file:

"--prefix::
  Path prefix where to install skytools files (default: /usr/local)
"

Best guess is your build put the files somewhere other then $libdir.



Finally it worked, added the 9.3 repository:

sudo tee /etc/apt/sources.list.d/pgdg.list <http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main
EOF
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | 
sudo apt-key add -

sudo apt-get update

then installed the packages:

sudo apt-get install postgresql-server-dev-9.3
sudo apt-get install postgresql-9.3-pgq3

then again:

./configure --prefix=/usr/local 
--with-pgconfig=/usr/lib/postgresql/9.3/bin/pg_config

make
sudo make install

then:

sudo su postgres

and:

londiste3 /home/leonardo/replication/pacsio_master.ini create-root 
master_node "dbname=test host=127.0.0.1"


Done!

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Londiste3 - Ubuntu 16.04 - Postgresql 9.3

[Leonardo M . Ramé]

El 03/06/16 a las 16:08, Leonardo M. Ramé escribió:

Hi again, I'm trying to create the root node using londiste3 from Ubuntu
16.04 connected to a PostgreSql 9.3 db, but I get this:

londiste3 /home/leonardo/replication/pacsio_master.ini create-root
master_node "dbname=test host=127.0.0.1"


2016-06-03 16:03:39,257 27600 INFO plpgsql is installed
2016-06-03 16:03:39,257 27600 INFO Installing pgq
2016-06-03 16:03:39,258 27600 INFO   Reading from
/usr/local/share/skytools3/pgq.sql
2016-06-03 16:03:40,013 27600 ERROR Job job_repli_test_master got error
on connection 'db': could not access file "$libdir/pgq_lowlevel": No
existe el archivo o el directorio.   Query: CREATE OR REPLACE FUNCTION
pgq.insert_event_raw(
 queue_n ...
Traceback (most recent call last):
   File "/usr/local/lib/python2.7/dist-packages/skytools/scripting.py",
line 579, in run_func_safely
 r = func()
   File
"/usr/local/lib/python2.7/dist-packages/skytools/adminscript.py", line
62, in work
 fn(*cmdargs)
   File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py",
line 147, in cmd_create_root
 return self.create_node('root', args)
   File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py",
line 198, in create_node
 self.install_code(db)
   File "/usr/local/lib/python2.7/dist-packages/londiste/setup.py", line
29, in install_code
 CascadeAdmin.install_code(self, db)
   File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py",
line 425, in install_code
 skytools.db_install(db.cursor(), objs, self.log)
   File "/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py",
line 531, in db_install
 obj.create(curs, log)
   File "/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py",
line 490, in create
 curs.execute(stmt)
   File "/usr/local/lib/python2.7/dist-packages/psycopg2/extras.py",
line 120, in execute
 return super(DictCursor, self).execute(query, vars)
OperationalError: could not access file "$libdir/pgq_lowlevel": No
existe el archivo o el directorio

P.S.: Here there's a suggestion of installing postgresql-9.4-pgq3, but
as I have 9.3 I cannot install the recommended package (it will install
9.4 and start a new cluster).



Sorry, I meant: Here 
https://blog.lateral.io/2015/09/postgresql-replication-with-londiste-from-skytools-3/ 



--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Londiste3 - Ubuntu 16.04 - Postgresql 9.3

[Leonardo M . Ramé]

Hi again, I'm trying to create the root node using londiste3 from Ubuntu 
16.04 connected to a PostgreSql 9.3 db, but I get this:


londiste3 /home/leonardo/replication/pacsio_master.ini create-root 
master_node "dbname=test host=127.0.0.1"



2016-06-03 16:03:39,257 27600 INFO plpgsql is installed
2016-06-03 16:03:39,257 27600 INFO Installing pgq
2016-06-03 16:03:39,258 27600 INFO   Reading from 
/usr/local/share/skytools3/pgq.sql
2016-06-03 16:03:40,013 27600 ERROR Job job_repli_test_master got error 
on connection 'db': could not access file "$libdir/pgq_lowlevel": No 
existe el archivo o el directorio.   Query: CREATE OR REPLACE FUNCTION 
pgq.insert_event_raw(

queue_n ...
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/skytools/scripting.py", 
line 579, in run_func_safely

r = func()
  File 
"/usr/local/lib/python2.7/dist-packages/skytools/adminscript.py", line 
62, in work

fn(*cmdargs)
  File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py", 
line 147, in cmd_create_root

return self.create_node('root', args)
  File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py", 
line 198, in create_node

self.install_code(db)
  File "/usr/local/lib/python2.7/dist-packages/londiste/setup.py", line 
29, in install_code

CascadeAdmin.install_code(self, db)
  File "/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py", 
line 425, in install_code

skytools.db_install(db.cursor(), objs, self.log)
  File "/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py", 
line 531, in db_install

obj.create(curs, log)
  File "/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py", 
line 490, in create

curs.execute(stmt)
  File "/usr/local/lib/python2.7/dist-packages/psycopg2/extras.py", 
line 120, in execute

return super(DictCursor, self).execute(query, vars)
OperationalError: could not access file "$libdir/pgq_lowlevel": No 
existe el archivo o el directorio


P.S.: Here there's a suggestion of installing postgresql-9.4-pgq3, but 
as I have 9.3 I cannot install the recommended package (it will install 
9.4 and start a new cluster).





Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] pgFoundry down

[Leonardo M . Ramé]

El 03/06/16 a las 14:10, Adrian Klaver escribió:

On 06/03/2016 08:42 AM, Leonardo M. Ramé wrote:

Hi, I'm trying to download Skytools 3.2 but pgFoundry seems to be down,
does anyone know another place to download it?.


I just tried it and got through:

http://pgfoundry.org/projects/skytools/


Thanks, downloaded. It looks like it was momentarily down.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgFoundry down

[Leonardo M . Ramé]

Hi, I'm trying to download Skytools 3.2 but pgFoundry seems to be down, 
does anyone know another place to download it?.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Londiste 3 pgq events_1_1 table huge

[Leonardo M . Ramé]

El 20/05/16 a las 10:19, Leonardo M. Ramé escribió:

El 19/05/16 a las 12:39, Saiful Muhajir escribió:

This has happened to us where we have dead or unmanaged consumer. Turns
out londiste is keeping the event even if the consumer is unreachable.
This is to ensure that the consumer gets what it should.

To clean this up, delete the unused/dead consumer, with qadmin or
manually if necessary. The table won't be deleted immediately though. We
have to restart pgqd and workers and wait for two days.




Thanks Rene and Saiful, I found two unused consumers, but after
"unregister consumer " those aren't deleted, what can I do to remove
them?.



Sorry, I successfully deleted them by using:

unregister consumer CONSUMER_NAME from QUEUE_NAME

The 2nd param QUEUE_NAME is a *must*.

Now I'm waiting for the events deletion...

Regards,

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Londiste 3 pgq events_1_1 table huge

[Leonardo M . Ramé]

El 19/05/16 a las 12:39, Saiful Muhajir escribió:

This has happened to us where we have dead or unmanaged consumer. Turns
out londiste is keeping the event even if the consumer is unreachable.
This is to ensure that the consumer gets what it should.

To clean this up, delete the unused/dead consumer, with qadmin or
manually if necessary. The table won't be deleted immediately though. We
have to restart pgqd and workers and wait for two days.




Thanks Rene and Saiful, I found two unused consumers, but after 
"unregister consumer " those aren't deleted, what can I do to remove 
them?.


--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Londiste 3 pgq events_1_1 table huge

[Leonardo M . Ramé]

El 18/05/16 a las 19:03, Rene . escribió:

Hi, Check for long running Idle in transaction sessions. Idle in transaction 
sessions may holding events table from cleaning itself up.
If there is more then days long running idle in transaction sessions, kill 
them, event table should be cleaned automatically after that.

"select pid,state, query_start from pg_stat_activity where state='idle in 
transaction';" for checking sessions.

Rene


Thanks Rene, I found only one "idle in transaction" and it dates from 
just a couple of minutes ago, so, the reason of huge event table must be 
something else.


By looking at the event_1_1 table I found records from march, but 
londiste3 status shows everything is already in sync:


nodo_master (root)
  |   Tables: 146/0/0
  |   Lag: 0s, Tick: 1112197
  +--: node_esclavo (leaf)
  Tables: 146/0/0
  Lag: 0s, Tick: 1112197

So, what if I manually delete old events?.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Londiste 3 pgq events_1_1 table huge

[Leonardo M . Ramé]

Hi, I couldn't find a mailing list or forum to ask londiste related 
questions, so I hope someone from this list can help me with this.


I have a Londiste based replication setup that is working perfectly 
since last year. Now I noted the events_1_1 table grew too much (almost 
exactly the same size of the whole database), so I'm asking how can I 
clean up the events table without breaking the replication?.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] HELP! Uninstalled wrong version of postgres

[Leonardo M . Ramé]

El 24/03/16 a las 14:19, Howard News escribió:

Hi,

I uninstalled the wrong version of postgres on Ubuntu using apt-get
remove postgresql-9.0, convinced that this was an old unused version.
You guess the rest...

The data files still appear to be there, all 485GB of them. Can these be
restored?

Thanks.



Ok, if the data files are still there I'd do this:

1) Assuming the data is in /var/lib/postgresql/9.0, rename that 
directory to /var/lib/9.0-old, AND COPY THAT DIRECTORY ELSEWHERE.
2) Reinstall 9.0 with "apt-get install postgresql-9.0". This should 
re-create the /var/lib/9.0 directory with an empty "main" dir.

3) Stop 9.0 with "pg_ctlcluster 9.0 main stop".
4) Rename the new directory /var/lib/9.0 to /var/lib/9.0-new
5) Rename the old dir (/var/lib/9.0-old) to /var/lib/9.0
6) Restart the cluster with "pg_ctlcluster 9.0 main start".

And everything should be fine again.

P.S.: All those steps should be done as root.

Regards,
--
Leonardo M. Ramé
http://leonardorame.blogspot.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] Partition

[Leonardo M . Ramé]

Hi, I have read and re-read the Partitioning chapter 
(http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html), but I 
still don't see how to implement this use case:


One table storing current data, let's call it the "master table", then 
one or more partitions with old data.


For example, the master table is this:

create table log(
  idlog serial not null,
  date_time timestamp not null default now(),
  log varchar(255),
  primary key(idlog)
);

The documentation says the master table should be empty, then a trigger 
must evaluate a condition, the date_time field for example, and insert 
the data in the corresponding table. This is a *rare* condition, because 
in the log example, new content is created without knowing its date and 
time in advance. For example:


insert into log(log) values('log this please.');

The date_time column will set the now() value.

Now, by following the example, to create a child table I'll do

create table log_old( ) inherits (log);

This raises the 1nst question, how can I define a *dynamic* check,
for checking older than X days?. Is this possible?.

An idea (didn't test):

check (date_time::date < now()::date - '30 day'::interval)

Then, the trigger, after each insert should *move* old data to log_old.

The only problem I see here is the master table isn't empty, but 
contains current data. The question is, will it work as expected?, I 
mean when I do "select * from log" I'll get an *union* of new and old data?.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Distributed Table Partitioning

[Leonardo M . Ramé]

El 13/03/16 a las 10:04, Peter J. Holzer escribió:

On 2016-03-12 21:19:11 -0500, Melvin Davidson wrote:

 - Original Message -
 From: "Leonardo M. Ramé" <l.r...@griensu.com>
 To: "PostgreSql-general" <pgsql-general@postgresql.org>
 Sent: Saturday, 12 March, 2016 8:25:01 PM
 Subject: [GENERAL] Distributed Table Partitioning

 I have this problem: a Master table containing records with a timestamp
 column registering creation date-time, and one Detail table containing
 info related to the Master table.

 As time went by, those tables grew enormously, and I can't afford
 expanding my SSD VPS. So I'm thinking about storing only NEW data into
 it, and move OLD data to a cheaper SATA VPS.

[...]

Why don't you just make use of tablespaces and partition the child tablespaces
so that the newer parttion is on the SSD and the older one is on SATA?


Since he mentioned virtual private servers (VPS) the reason might be
that his hoster offers VPS with SSDs (of various sizes) and VPS with
rotating hard disks (of various sizes), but not VPS with both. So he
can't rent a VPS with a relatively small SSD and a larger hard disk.

That might be a reason to look for an alternate hoster, but if he's
otherwise happy, switching to an unknown provider might be considered
too large a risk.

 hp



Yes, Peter is right, I must store one table in one VPS and the other in 
a 2nd VPS.


I'm thinking of partitioning the table, on local and the remote using a 
Foreign Data Wrapper, what do you think about this approach?.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Distributed Table Partitioning

[Leonardo M . Ramé]

I have this problem: a Master table containing records with a timestamp 
column registering creation date-time, and one Detail table containing 
info related to the Master table.


As time went by, those tables grew enormously, and I can't afford 
expanding my SSD VPS. So I'm thinking about storing only NEW data into 
it, and move OLD data to a cheaper SATA VPS.


The goal is using the SSD server as "main", and the other (or others?) 
as "child", so queries still go to the main server, it somehow detects 
which records must be fetched from it and what from the child servers, 
then return the "composed" dataset to the caller.


I think this is called Distributed Horizontal Table Partitioning.


Is there a way to do this without changing my application code?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Update foreign table with trigger

[Leonardo M . Ramé]

El 26/02/16 a las 17:11, s d escribió:

On 26 February 2016 at 21:02, Leonardo M. Ramé <l.r...@griensu.com
<mailto:l.r...@griensu.com>> wrote:



El 26/02/16 a las 16:49, s d escribió:

On 26 February 2016 at 20:42, Leonardo M. Ramé
<l.r...@griensu.com <mailto:l.r...@griensu.com>
<mailto:l.r...@griensu.com <mailto:l.r...@griensu.com>>> wrote:




 Then try to do the update on the remote db directly.

 In the meantime could you provide the table and trigger
definitions?


 I don't understand why the trigger is run in the caller
database
 instead of the called (foreign) one.


It isn't. You get this error message because the reason why the
local
command fails is in the remote trigger somewhere.


But, when I update TABLE_A from the remote server everything works ok.


I'm pretty sure we won't get further without seeing your defs.



Solved!.

The indeed the problem was at the foreign server side, instead of 
working with TABLE_B I had to do public.TABLE_B and everything went ok.





--
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] Update foreign table with trigger

[Leonardo M . Ramé]

El 26/02/16 a las 16:49, s d escribió:

On 26 February 2016 at 20:42, Leonardo M. Ramé <l.r...@griensu.com
<mailto:l.r...@griensu.com>> wrote:




Then try to do the update on the remote db directly.

In the meantime could you provide the table and trigger definitions?


I don't understand why the trigger is run in the caller database
instead of the called (foreign) one.


It isn't. You get this error message because the reason why the local
command fails is in the remote trigger somewhere.



But, when I update TABLE_A from the remote server everything works ok.


--
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] Update foreign table with trigger

[Leonardo M . Ramé]

El 26/02/16 a las 16:33, s d escribió:

On 26 February 2016 at 20:19, Leonardo M. Ramé <l.r...@griensu.com
<mailto:l.r...@griensu.com>> wrote:



El 26/02/16 a las 16:18, s d escribió:


On 26 February 2016 at 20:02, Leonardo M. Ramé
<l.r...@griensu.com <mailto:l.r...@griensu.com>
<mailto:l.r...@griensu.com <mailto:l.r...@griensu.com>>> wrote:


 El 26/02/16 a las 15:55, John R Pierce escribió:

     On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:

 Hi, I created a Postgres_FDW table (TABLE_A) and
need to do
 an update on that table.

 As TABLE_A has a trigger, and the trigger does an
insert on
 another table (TABLE_B), I had to create another
foreign
 table called TABLE_B, that's ok.



 that trigger is defined on the server that actually has
table_a,
 right?   or did you define a trigger on the FDW table ?

 Hi John, yes, the trigger is only defined on the foreign
server.


Let's check we get this right!
You have two "real" table in the remote server with a trigger
doing it's
job on them and on the local server you have and FDW on each remote
table. Right?


Yes, that's right.


Then try to do the update on the remote db directly.

In the meantime could you provide the table and trigger definitions?



I don't understand why the trigger is run in the caller database instead 
of the called (foreign) one.



--
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] Update foreign table with trigger

[Leonardo M . Ramé]

El 26/02/16 a las 16:18, s d escribió:


On 26 February 2016 at 20:02, Leonardo M. Ramé <l.r...@griensu.com
<mailto:l.r...@griensu.com>> wrote:


El 26/02/16 a las 15:55, John R Pierce escribió:

On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:

Hi, I created a Postgres_FDW table (TABLE_A) and need to do
an update on that table.

As TABLE_A has a trigger, and the trigger does an insert on
another table (TABLE_B), I had to create another foreign
table called TABLE_B, that's ok.



that trigger is defined on the server that actually has table_a,
right?   or did you define a trigger on the FDW table ?

Hi John, yes, the trigger is only defined on the foreign server.


Let's check we get this right!
You have two "real" table in the remote server with a trigger doing it's
job on them and on the local server you have and FDW on each remote
table. Right?



Yes, that's right.


--
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] Possible bug in psql 9.4.4

[Leonardo M . Ramé]

El 26/02/16 a las 15:59, Leonardo M. Ramé escribió:

It looks like psql 9.4.4 has a bug when trying to display table info
using \d tablename.

On 9.4.2 that command displays Indexes, Constraints, Triggers, etc.
9.4.4 only displays the table fields.

Regards,


Sorry, option \t (Tuples Only) must be off to display that info.



--
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] Update foreign table with trigger

[Leonardo M . Ramé]

El 26/02/16 a las 15:55, John R Pierce escribió:

On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:
Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update 
on that table.


As TABLE_A has a trigger, and the trigger does an insert on another 
table (TABLE_B), I had to create another foreign table called 
TABLE_B, that's ok. 



that trigger is defined on the server that actually has table_a, 
right?   or did you define a trigger on the FDW table ?



Hi John, yes, the trigger is only defined on the foreign server.


--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Possible bug in psql 9.4.4

[Leonardo M . Ramé]

It looks like psql 9.4.4 has a bug when trying to display table info 
using \d tablename.


On 9.4.2 that command displays Indexes, Constraints, Triggers, etc. 
9.4.4 only displays the table fields.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Update foreign table with trigger

[Leonardo M . Ramé]

Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on 
that table.


As TABLE_A has a trigger, and the trigger does an insert on another 
table (TABLE_B), I had to create another foreign table called TABLE_B, 
that's ok.


At this point I have two foreign tables, TABLE_A and TABLE_B.

Now, when I try to do an update on TABLE_A, I get relation "TABLE_B" 
does not exist.


Here's what I get:

update TABLE_A set updated=true where id=1234;

ERROR:  relation "TABLE_B" does not exist
CONTEXT:  Remote SQL command: UPDATE public.TABLE_A SET updated = $2 
WHERE ctid = $1

PL/pgSQL function public.TABLE_A_update() line 4 at SQL statement

Any hint?.


Regards
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Ubuntu 15.04 Installing Oracle_fdw

[Leonardo M . Ramé]

Hi, I compiled oracle_fdw.so on Ubuntu 15.04 Server 64bits and I'm 
getting the "No such file or directory" error when I try to create the 
oracle_fdw extension.


After doing make and make install on Oracle fdw source oracle_fdw.so is 
built and installed correctly (oracle_fdw.so is copied to my $libdir 
directory those related files are copied to the extension directory:


ls -lah /usr/share/postgresql/9.4/extension

-rw-r--r-- 1 root root  231 ene  2 12:19 oracle_fdw--1.0--1.1.sql
-rw-r--r-- 1 root root 1003 ene  2 12:19 oracle_fdw--1.1.sql
-rw-r--r-- 1 root root  133 ene  2 12:19 oracle_fdw.control

But, when I try to create the extension I get this:

psql (9.4.4, server 9.4.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)

Type "help" for help.

postgres=# create extension oracle_fdw;
ERROR:  could not open extension control file 
"/usr/share/postgresql/9.4/extension/oracle_fdw.control": No such file 
or directory



Any hint?
--
Leonardo M. Ramé
http://leonardorame.blogspot.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Ubuntu 15.04 Installing Oracle_fdw

[Leonardo M . Ramé]

El 02/01/16 a las 12:51, Leonardo M. Ramé escribió:

Hi, I compiled oracle_fdw.so on Ubuntu 15.04 Server 64bits and I'm
getting the "No such file or directory" error when I try to create the
oracle_fdw extension.

After doing make and make install on Oracle fdw source oracle_fdw.so is
built and installed correctly (oracle_fdw.so is copied to my $libdir
directory those related files are copied to the extension directory:

ls -lah /usr/share/postgresql/9.4/extension

-rw-r--r-- 1 root root  231 ene  2 12:19 oracle_fdw--1.0--1.1.sql
-rw-r--r-- 1 root root 1003 ene  2 12:19 oracle_fdw--1.1.sql
-rw-r--r-- 1 root root  133 ene  2 12:19 oracle_fdw.control

But, when I try to create the extension I get this:

psql (9.4.4, server 9.4.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Type "help" for help.

postgres=# create extension oracle_fdw;
ERROR:  could not open extension control file
"/usr/share/postgresql/9.4/extension/oracle_fdw.control": No such file
or directory


Any hint?


hehe, sorry, I was connected to a remote host using psql.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Could not connect to server: No buffer space available (0x00002747/10055)

[Leonardo M . Ramé]

Hi, I installed postgresql-x64-9.4 (installed from 
postgresql-9.4.5-2-windows-x64.exe) on a Windows Seven 64bits PC, and 
I'm getting the error "Could not connect to server: No buffer space 
available (0x2747/10055)" when I only have 4 or 5 connections.


Can anoyone help me fix this?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Could not connect to server: No buffer space available (0x00002747/10055)

[Leonardo M . Ramé]

El 02/12/15 a las 13:20, Adrian Klaver escribió:

On 12/02/2015 07:53 AM, Leonardo M. Ramé wrote:

Hi, I installed postgresql-x64-9.4 (installed from
postgresql-9.4.5-2-windows-x64.exe) on a Windows Seven 64bits PC, and
I'm getting the error "Could not connect to server: No buffer space
available (0x2747/10055)" when I only have 4 or 5 connections.

Can anoyone help me fix this?.


Not without more information.

What are you doing in those 4-5 connections?

What does the Postgres log show before and after the error?

Pretty sure the error is not coming from Postgres, so where do you see
the error message?

Do you have any firewalls, anti-virus software running?



Thanks, uninstalled antivirus (Avast) and the error dissapeared.


--
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] Array_to_json remove brackets

[Leonardo M . Ramé]

El 13/11/15 a las 10:49, Merlin Moncure escribió:

On Fri, Nov 13, 2015 at 7:20 AM, Leonardo M. Ramé <l.r...@griensu.com> wrote:

Hi, is there a way to get an array converted to json without brackets?.

I'm getting, for example [{"field": "value"}, {"field": "value"}] and I want
to get this: {"field": "value"}, {"field": "value"}.


please supply an example.  Also,
{"field": "value"}, {"field": "value"}

is not valid json, so, basically, no.  But you could textually remove
them I guess but I'm thinking more context is needed to give a better
answer.

merlin




Thanks, I fixed this with a simple trim command:

select trim('[abcd]', '[]');


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Array_to_json remove brackets

[Leonardo M . Ramé]

Hi, is there a way to get an array converted to json without brackets?.

I'm getting, for example [{"field": "value"}, {"field": "value"}] and I 
want to get this: {"field": "value"}, {"field": "value"}.


Regards,

--
Leonardo M. Ramé
http://leonardorame.blogspot.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] MinGW-W64 compile error

[Leonardo M . Ramé]

El 04/11/15 a las 00:05, Tom Lane escribió:

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?="  writes:

Hi, I'm trying to build the client library of PostgreSql 9.3.x using
this version of MinGW's gcc:
...
g++  -DFRONTEND -I../../src/include -I./src/include/port/win32
-DEXEC_BACKEND  "-I../../src/include/port/win32" -DBUILDING_DLL  -c -o
relpath.o relpath.c


Why is it invoking g++ and not gcc?


I don't know. I just installed MinGW, then ./configure, maybe there's an 
error in the configure script?.





relpath.c: In function 'int forkname_chars(const char*, ForkNumber*)':
relpath.c:55:15: error: invalid conversion from 'int' to 'ForkNumber'
[-fpermissive]
for (forkNum = 1; forkNum <= MAX_FORKNUM; forkNum++)
relpath.c:55:51: error: no 'operator++(int)' declared for postfix '++'
[-fpermissive]
for (forkNum = 1; forkNum <= MAX_FORKNUM; forkNum++)


The second of these definitely comes from trying to compile C code as C++,
and I think the first does too.

regards, tom lane







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] MinGW-W64 compile error

[Leonardo M . Ramé]

El 04/11/15 a las 06:00, Leonardo M. Ramé escribió:


El 04/11/15 a las 00:05, Tom Lane escribió:

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" <l.r...@griensu.com> writes:

Hi, I'm trying to build the client library of PostgreSql 9.3.x using
this version of MinGW's gcc:


Nevermind, deleted my MinGW instalation, then installed msys and 
MinGW-W64 again, defined /etc/fstab to my MinGW/mingw32/bin directory 
ans voila!, it compiled without errors.


Regards,
Leonardo.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] MinGW-W64 compile error

[Leonardo M . Ramé]

Hi, I'm trying to build the client library of PostgreSql 9.3.x using 
this version of MinGW's gcc:


gcc.exe (i686-posix-dwarf-rev0, Built by MinGW-W64 project) 5.2.0

I did a ./configure --without-zlib

then make (mingw32-make.exe), and got this:

$ /e/MinGW/mingw32/bin/mingw32-make.exe
E:/MinGW/mingw32/bin/mingw32-make -C src all
mingw32-make[1]: Entering directory 'E:/postgresql-9.3.10/src'
E:/MinGW/mingw32/bin/mingw32-make -C common all
mingw32-make[2]: Entering directory 'E:/postgresql-9.3.10/src/common'
E:/MinGW/mingw32/bin/mingw32-make -C ../backend submake-errcodes
mingw32-make[3]: Entering directory 'E:/postgresql-9.3.10/src/backend'
mingw32-make[3]: Nothing to be done for 'submake-errcodes'.
mingw32-make[3]: Leaving directory 'E:/postgresql-9.3.10/src/backend'
g++  -DFRONTEND -I../../src/include -I./src/include/port/win32 
-DEXEC_BACKEND  "-I../../src/include/port/win32" -DBUILDING_DLL  -c -o 
relpath.o relpath.c

relpath.c: In function 'int forkname_chars(const char*, ForkNumber*)':
relpath.c:55:15: error: invalid conversion from 'int' to 'ForkNumber' 
[-fpermissive]

  for (forkNum = 1; forkNum <= MAX_FORKNUM; forkNum++)
   ^
relpath.c:55:51: error: no 'operator++(int)' declared for postfix '++' 
[-fpermissive]

  for (forkNum = 1; forkNum <= MAX_FORKNUM; forkNum++)
   ^
: recipe for target 'relpath.o' failed
mingw32-make[2]: *** [relpath.o] Error 1
mingw32-make[2]: Leaving directory 'E:/postgresql-9.3.10/src/common'
makefile:34: recipe for target 'all-common-recurse' failed
mingw32-make[1]: *** [all-common-recurse] Error 2
mingw32-make[1]: Leaving directory 'E:/postgresql-9.3.10/src'
GNUmakefile:11: recipe for target 'all-src-recurse' failed
mingw32-make: *** [all-src-recurse] Error 2

Does anyone know how can I get rid of this error?.

Regards,

--
Leonardo M. Ramé
http://leonardorame.blogspot.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] Compression function

[Leonardo M. Ramé]

Hi, does anyone know if there's a compression function to let me store 
in gzipped/deflate format TEXT or Bytea fields.


Please correct me if I'm wrong, but I also wonder if this function is 
really needed since I've read large objects are stored with TOAST, hence 
compression is already there.



Regards,
--
Leonardo M. Ramé
http://leonardorame.blogspot.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column does not exists?

[Leonardo M. Ramé]

El 26/03/15 a las 14:17, Ashesh Vashi escibió:



[Sent through mobile]
On Mar 26, 2015 10:43 PM, Leonardo M. Ramé l.r...@griensu.com
mailto:l.r...@griensu.com wrote:
 
  Ok, I have this table:
 
  CREATE TABLE sessions
  (
SESSIONID integer NOT NULL,
SESSIONTIMESTAMP character varying(45) NOT NULL,
SESSIONDATA character varying(200) DEFAULT NULL::character varying,
CONSTRAINT sessions_pkey PRIMARY KEY (SESSIONID)
  )
 
  Now, when I do:
 
  DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10:02:02'
 


Hi Ashen, adding ::timestamp does not solve the problem, the issue is 
solved by adding double quotes to the field name. Anyway, I would like 
to know why the error.



--
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] Column does not exists?

[Leonardo M. Ramé]

El 26/03/15 a las 14:23, Francisco Olarte escibió:

Hi Leonardo:

On Thu, Mar 26, 2015 at 6:12 PM, Leonardo M. Ramé l.r...@griensu.com wrote:

DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10:02:02'
ERROR:  column sessiontimestamp does not exist
LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10...

...

DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10:02:02'

It DOES work.

Why the db doesn't recognize the name of the table without quotes?.


Unquoted identifiers for several things, column names amongst them,
are treated by case folding in SQL. Many DBs do it to uppercase,
postgres does it to lower case ( as hinted by the column name being
printed in lowercase ). So if you QUOTE an UPPERCASE name you must
quote it always.

As a rule of thumb, I'll recommend quoting your identifiers always or
never, quoting it in some statements ( create ) and not others ( 1st
delete ) will normally surprise you on unpleasant ways.

Francisco Olarte.



Aha, the problem, then, was caused by the Create statement. This table 
was copied from a MySql dump where all columns were named column.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Column does not exists?

[Leonardo M. Ramé]

Ok, I have this table:

CREATE TABLE sessions
(
  SESSIONID integer NOT NULL,
  SESSIONTIMESTAMP character varying(45) NOT NULL,
  SESSIONDATA character varying(200) DEFAULT NULL::character varying,
  CONSTRAINT sessions_pkey PRIMARY KEY (SESSIONID)
)

Now, when I do:

DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10:02:02'

I get:

ERROR:  column sessiontimestamp does not exist
LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10...
   ^
** Error **

ERROR: column sessiontimestamp does not exist
SQL state: 42703
Character: 28

But if I do:

DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10:02:02'

It DOES work.

Why the db doesn't recognize the name of the table without quotes?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Column does not exists?

[Leonardo M. Ramé]

El 26/03/15 a las 14:18, Adrian Klaver escibió:

On 03/26/2015 10:12 AM, Leonardo M. Ramé wrote:

Ok, I have this table:

CREATE TABLE sessions
(
   SESSIONID integer NOT NULL,
   SESSIONTIMESTAMP character varying(45) NOT NULL,
   SESSIONDATA character varying(200) DEFAULT NULL::character varying,
   CONSTRAINT sessions_pkey PRIMARY KEY (SESSIONID)
)

Now, when I do:

DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10:02:02'

I get:

ERROR:  column sessiontimestamp does not exist
LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10...
^
** Error **

ERROR: column sessiontimestamp does not exist
SQL state: 42703
Character: 28

But if I do:

DELETE From sessions WHERE SESSIONTIMESTAMP  '2010-01-01 10:02:02'

It DOES work.

Why the db doesn't recognize the name of the table without quotes?.


See here, bottom of 4.1.1. Identifiers and Key Words:

http://www.postgresql.org/docs/9.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS



Thanks, then it looks like SESSIONTIMESTAMP is an identifier?.


--
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] Timezone mismatch

[Leonardo M. Ramé]

El 20/03/15 a las 12:38, Steve Crawford escibió:

On 03/20/2015 08:29 AM, Leonardo M. Ramé wrote:
Hi, I had to change the O.S. timezone and aparently PostgreSql 
continues using the old timezone, how can I force update it's time 
zone?.


Using PostgreSql 8.4 on Ubuntu Server 12.04.

To update the OS timezone I used sudo dpkg-reconfigure tzdata 


Did you reload/restart PostgreSQL after making the change?

What is the setting of timezone in postgresql.conf?

What is set as the timezone on the *client* side? (That's really more 
relevant for timestamp_tz data.)


Cheers,
Steve




Hi Steve, I did a select pg_reload_conf(); from psql. Is this enough?.

In postgresql.conf I have timezone and timezone_abbreviations commented, 
as default.


From client side I never set any timezone related parameter.

Leonardo.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Timezone mismatch

[Leonardo M. Ramé]

Hi, I had to change the O.S. timezone and aparently PostgreSql continues 
using the old timezone, how can I force update it's time zone?.


Using PostgreSql 8.4 on Ubuntu Server 12.04.

To update the OS timezone I used sudo dpkg-reconfigure tzdata

Regards,

--
Leonardo M. Ramé
http://leonardorame.blogspot.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Timezone mismatch

[Leonardo M. Ramé]

El 20/03/15 a las 14:11, Leonardo M. Ramé escibió:

El 20/03/15 a las 13:03, Adrian Klaver escibió:

I am not sure what the exact issue is?

How are you determining that the new time zone is not being used?

What was the old time zone, what is the new one?
I don't know if the new time zone is beign used, It just coincede with 
the old local time zone which was -3 hours compared to UTC.


Old time zone was:

Current default time zone: 'America/Argentina/Cordoba'
Local time is now: Fri Mar 20 06:46:58 ART 2015.
Universal Time is now: Fri Mar 20 09:46:58 UTC 2015.

New time zone is:

Current default time zone: 'Etc/UTC'
Local time is now: Fri Mar 20 09:47:48 UTC 2015.
Universal Time is now: Fri Mar 20 09:47:48 UTC 2015.




Well, pg_load_conf didn't reoload the timezone config from 
postgresql.conf, I had to restart the service to be changed.


Leonardo.



--
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] Sequences not created, bug in pg_dump?

[Leonardo M. Ramé]

El 19/03/15 a las 14:13, Adrian Klaver escibió:

On 03/19/2015 10:02 AM, Leonardo M. Ramé wrote:


El 19/03/15 a las 13:09, Adrian Klaver escibió:

On 03/19/2015 08:43 AM, Leonardo M. Ramé wrote:


Hi, I'm creating a database dump excluding one table and found only the
sequences created implicitly (using serial type) are created when I
restore the dump.

The command I use is: pg_dump -T table_to_be_excluded mydb

I understand all related objects to the table to be excluded are not
dumpled, but why I don't get any CREATE SEQUENCE command in my dump?.



What version of Postgres?

If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they
do not show up in the dump file?

It works for me here on 9.3.



Yes that's the problem. The dump is performed using 9.3.5 on windows.


I can not replicate:

aklaver@test= create sequence test_seq;

aklaver@test= \d
List of relations
  Schema |  Name   |   Type   |  Owner
+-+--+--
  public | CamelCap_Quoted | table| aklaver
  public | app_sessions| table| aklaver
  public | app_users   | table| aklaver
  public | app_users_vw| view | aklaver
  public | app_val_session_vw  | view | aklaver
  public | camelcap_not_quoted | table| aklaver
  public | float_test  | table| postgres
  public | ins_test| table| aklaver
  public | mytable_is_not_readonly | table| aklaver
  public | mytable_is_readonly | table| aklaver
  public | on_duty | table| aklaver
  public | on_duty_id_seq  | sequence | aklaver
  public | seq_counter | table| aklaver
  public | t   | table| postgres
  public | t_id_seq| sequence | postgres
  public | tasks   | table| aklaver
  public | tasks2  | table| aklaver
  public | tasks_task_id_seq   | sequence | aklaver
  public | tbl_test| table| aklaver
  public | test_seq| sequence | aklaver


/usr/local/pgsql93/bin/pg_dump -T app_sessions -U postgres -p 5452 test
  test_txt.sql


In test_txt.sql:

--
-- Name: test_seq; Type: SEQUENCE; Schema: public; Owner: aklaver
--

CREATE SEQUENCE test_seq
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;

Do you see any warnings/errors when you run the dump?



Sorry for answering too late, but the problem was solved by using 
pg_dump command line instead of doing the backup from pgAdmin. So I 
should change the subject of this thread, s/in pg_dump/in pg_admin/g :)


Regards,
Leonardo.


--
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] Timezone mismatch

[Leonardo M. Ramé]

El 20/03/15 a las 13:03, Adrian Klaver escibió:

I am not sure what the exact issue is?

How are you determining that the new time zone is not being used?

What was the old time zone, what is the new one?
I don't know if the new time zone is beign used, It just coincede with 
the old local time zone which was -3 hours compared to UTC.


Old time zone was:

Current default time zone: 'America/Argentina/Cordoba'
Local time is now: Fri Mar 20 06:46:58 ART 2015.
Universal Time is now: Fri Mar 20 09:46:58 UTC 2015.

New time zone is:

Current default time zone: 'Etc/UTC'
Local time is now: Fri Mar 20 09:47:48 UTC 2015.
Universal Time is now: Fri Mar 20 09:47:48 UTC 2015.




--
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] Sequences not created, bug in pg_dump?

[Leonardo M. Ramé]

El 19/03/15 a las 13:09, Adrian Klaver escibió:

On 03/19/2015 08:43 AM, Leonardo M. Ramé wrote:


Hi, I'm creating a database dump excluding one table and found only the
sequences created implicitly (using serial type) are created when I
restore the dump.

The command I use is: pg_dump -T table_to_be_excluded mydb

I understand all related objects to the table to be excluded are not
dumpled, but why I don't get any CREATE SEQUENCE command in my dump?.



What version of Postgres?

If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they
do not show up in the dump file?

It works for me here on 9.3.



Yes that's the problem. The dump is performed using 9.3.5 on windows.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Sequences not created, bug in pg_dump?

[Leonardo M. Ramé]

Hi, I'm creating a database dump excluding one table and found only the 
sequences created implicitly (using serial type) are created when I 
restore the dump.


The command I use is: pg_dump -T table_to_be_excluded mydb

I understand all related objects to the table to be excluded are not 
dumpled, but why I don't get any CREATE SEQUENCE command in my dump?.


--
Leonardo M. Ramé
http://leonardorame.blogspot.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Weight BLOB objects in postgreSQL? How?

[Leonardo M. Ramé]

Here's the answer.

http://pdenya.com/2014/01/16/postgres-bytea-size/

El 04/03/15 a las 12:17, John R Pierce escibió:

On 3/4/2015 7:03 AM, María Griensu wrote:
I need to figure out how can I weight BLOB objects in a table of a 
DB, I'm not expert on this topics, so I appreciate any help you can 
give me.


postgres's equivalent of BLOB is BYTEA. I'm not sure what you mean 
by 'weight' here.


--
john r pierce  37N 122W
somewhere on the middle of the left coast

[GENERAL] psql generate insert command based on select

[Leonardo M. Ramé]

Hi, today I needed to re-create certain records deleted from a mysql 
database, so I restored an old backup, opened a terminal and logged in 
to the old database using the mysql command line utility, then opened 
a new terminal with mysql connected to the production database. Then did 
a select * from table where id=xxx \G; to display a record, then, on 
the other terminal I had to write insert into table(field1, 
field2,...,fieldN) values(...); for each record.


While doing that I tought of a neat feature that psql could provide, 
that is something like \insert for select * from table where id=xxx; 
this should create the insert command for the requested query.


Is such a thing already present in psql?.

Regards,

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



--
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] psql generate insert command based on select

[Leonardo M. Ramé]

El 10/10/14 a las 14:37, Adrian Klaver escibió:

On 10/10/2014 10:27 AM, Leonardo M. Ramé wrote:

Hi, today I needed to re-create certain records deleted from a mysql
database, so I restored an old backup, opened a terminal and logged in
to the old database using the mysql command line utility, then opened
a new terminal with mysql connected to the production database. Then did
a select * from table where id=xxx \G; to display a record, then, on
the other terminal I had to write insert into table(field1,
field2,...,fieldN) values(...); for each record.

While doing that I tought of a neat feature that psql could provide,
that is something like \insert for select * from table where id=xxx;
this should create the insert command for the requested query.

Is such a thing already present in psql?.


I may be missing something but:

http://www.postgresql.org/docs/9.3/interactive/sql-insert.html

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod  '2004-05-07';

or are you thinking of something that takes a SELECT query and turns 
it into a series of INSERT queries.


The only way I can of doing this is to use pg_dump -t some_table -a 
--inserts or --column-inserts


The problem is I needed the make the insert statements in another 
database, not the one I was connected to for soing the select.


The pg_dump could help in part, because after creating it I need to 
delete all the unneeded records.


--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



--
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] psql generate insert command based on select

[Leonardo M. Ramé]

El 10/10/14 a las 14:50, vibhor.ku...@enterprisedb.com escibió:


On Oct 10, 2014, at 1:27 PM, Leonardo M. Ramé l.r...@griensu.com wrote:


Hi, today I needed to re-create certain records deleted from a mysql database, so I restored an old backup, 
opened a terminal and logged in to the old database using the mysql command line utility, then 
opened a new terminal with mysql connected to the production database. Then did a select * from table 
where id=xxx \G; to display a record, then, on the other terminal I had to write insert into 
table(field1, field2,...,fieldN) values(...); for each record.

While doing that I tought of a neat feature that psql could provide, that is something 
like \insert for select * from table where id=xxx; this should create the 
insert command for the requested query.


You can do something like given below:
CREATE TABLE temp_generate_inserts AS SELECT * FROM table id=xx
Then use pg_dump --column-inserts  -t temp_generate_inserts db1|psql db2
and later you can drop temp_generate_inserts table.

With this you can also explore dblink_build_sql_insert function which comes 
with dblink module:
http://www.postgresql.org/docs/9.3/interactive/contrib-dblink-build-sql-insert.html

Thanks  Regards,
Vibhor Kumar
(EDB) EnterpriseDB Corporation
The Postgres Database Company
Blog:http://vibhork.blogspot.com



Nice!, I didn't know the create table...as select... command.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Error while upgrading from 8.4 to 9.3

[Leonardo M . Ramé]

Hi, we are trying to upgrade from 8.4 to 9.3, but at the end of the
process we've got the error:

command: /usr/lib/postgresql/9.3/bin/pg_resetxlog -o 2429057 
/mnt/datos/datos_9.3/main  pg_upgrade_utility.log 21
pg_resetxlog: lock file «postmaster.pid» exists
Is a server running?  If not, delete the lock file and try again.

Our setup is this:
Server: Ubuntu 12.04 – 32 bits
Clusters: Postgresql 8.4 y Postgresql 9.3
 
The steps we followed are:

1) pg_upgrade using –-check result Ok.
2) pg_upgrade without --check

Everything went ok, data directory was copied without problems.

At the end, we got the error I mentioned at the beggining of this email.

P.S.: pg_upgrade_utility.log attached. 

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


-
  pg_upgrade run on Wed May 28 08:03:08 2014
-


-
  pg_upgrade run on Wed May 28 08:03:35 2014
-

command: /usr/lib/postgresql/9.3/bin/pg_dumpall --port 50432 --username 
postgres --schema-only --globals-only --quote-all-identifiers 
--binary-upgrade  -f pg_upgrade_dump_globals.sql  pg_upgrade_utility.log 
21


command: /usr/lib/postgresql/9.3/bin/vacuumdb --host /tmp/toto --port 50432 
--username postgres --all --analyze   pg_upgrade_utility.log 21
vacuumdb: limpiando la base de datos «postgres»
vacuumdb: limpiando la base de datos «template1»


command: /usr/lib/postgresql/9.3/bin/vacuumdb --host /tmp/toto --port 50432 
--username postgres --all --freeze   pg_upgrade_utility.log 21
vacuumdb: limpiando la base de datos «postgres»
vacuumdb: limpiando la base de datos «template1»


command: cp -Rf /mnt/datos/pg_clog /mnt/datos/datos_9.3/main/pg_clog  
pg_upgrade_utility.log 21


command: /usr/lib/postgresql/9.3/bin/pg_resetxlog -f -x 787620816 
/mnt/datos/datos_9.3/main  pg_upgrade_utility.log 21
Bitácora de transacciones reiniciada


command: /usr/lib/postgresql/9.3/bin/pg_resetxlog -m 24527,24526 
/mnt/datos/datos_9.3/main  pg_upgrade_utility.log 21
Bitácora de transacciones reiniciada


command: /usr/lib/postgresql/9.3/bin/pg_resetxlog -l 048A008E 
/mnt/datos/datos_9.3/main  pg_upgrade_utility.log 21
Bitácora de transacciones reiniciada


command: /usr/lib/postgresql/9.3/bin/psql --echo-queries --set 
ON_ERROR_STOP=on --no-psqlrc --dbname=template1 --host /tmp/toto --port 50432 
--username postgres -f pg_upgrade_dump_globals.sql  
pg_upgrade_utility.log 21
SET default_transaction_read_only = off;
SET
SET client_encoding = 'UTF8';
SET
SET standard_conforming_strings = off;
SET
SET escape_string_warning = off;
SET
SELECT binary_upgrade.set_next_pg_authid_oid('343529'::pg_catalog.oid);
 set_next_pg_authid_oid 

 
(1 fila)

CREATE ROLE ernesto;
CREATE ROLE
ALTER ROLE ernesto WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
NOREPLICATION PASSWORD 'md5420d7930f9b0f4c3c5c3f09116c21136' VALID UNTIL 
'infinity';
ALTER ROLE
SELECT binary_upgrade.set_next_pg_authid_oid('10'::pg_catalog.oid);
 set_next_pg_authid_oid 

 
(1 fila)

ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN 
NOREPLICATION;
ALTER ROLE
SELECT binary_upgrade.set_next_pg_authid_oid('74416'::pg_catalog.oid);
 set_next_pg_authid_oid 

 
(1 fila)

CREATE ROLE uriel;
CREATE ROLE
ALTER ROLE uriel WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
NOREPLICATION PASSWORD 'md594ebb9ea1bb345513055cb09316a8c6e';
ALTER ROLE
SELECT binary_upgrade.set_next_pg_authid_oid('74417'::pg_catalog.oid);
 set_next_pg_authid_oid 

 
(1 fila)

CREATE ROLE vmedica;
CREATE ROLE
ALTER ROLE vmedica WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
NOREPLICATION PASSWORD 'md5af39c68c2283201f3067fd5ff3c33161';
ALTER ROLE
CREATE DATABASE mirthdb WITH TEMPLATE = template0 OWNER = postgres;
CREATE DATABASE
UPDATE pg_catalog.pg_database SET datfrozenxid = '599655521' WHERE datname = 
'mirthdb';
UPDATE 1
CREATE DATABASE ris WITH TEMPLATE = template0 OWNER = postgres;
CREATE DATABASE
UPDATE pg_catalog.pg_database SET datfrozenxid = '599655521' WHERE datname = 
'ris';
UPDATE 1
REVOKE ALL ON DATABASE ris FROM PUBLIC;
REVOKE
REVOKE ALL ON DATABASE ris FROM postgres;
REVOKE
GRANT ALL ON DATABASE ris TO postgres;
GRANT
GRANT CONNECT,TEMPORARY ON DATABASE ris TO PUBLIC;
GRANT
GRANT ALL ON DATABASE ris TO uriel;
GRANT
GRANT CONNECT ON DATABASE ris TO ernesto;
GRANT
CREATE DATABASE ris_20120831_0200 WITH TEMPLATE = template0 OWNER = 
postgres;
CREATE DATABASE
UPDATE pg_catalog.pg_database SET datfrozenxid = '599655521' WHERE datname = 
'ris_20120831_0200';
UPDATE 1
CREATE DATABASE ris_test WITH TEMPLATE

Re: [GENERAL] Error while upgrading from 8.4 to 9.3

[Leonardo M . Ramé]

On 2014-05-28 07:39:33 -0700, Adrian Klaver wrote:
 On 05/28/2014 07:24 AM, Claudio Biasatti wrote:
 with  check
 
 /usr/lib/postgresql/9.3/bin/pg_upgrade --check -b
 /usr/lib/postgresql/8.4/bin/ -B /usr/lib/postgresql/9.3/bin/ -d
 /mnt/datos/ -D /mnt/datos/datos_9.3/main/ -o ' -c
 config_file=/etc/postgresql/8.4/main/postgresql.conf' -O ' -c
 config_file=/etc/postgresql/9.3/main/postgresql.conf'
 
 
 without check
 
 /usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/8.4/bin/
 -B /usr/lib/postgresql/9.3/bin/ -d /mnt/datos/ -D
 /mnt/datos/datos_9.3/main/ -o ' -c
 config_file=/etc/postgresql/8.4/main/postgresql.conf' -O ' -c
 config_file=/etc/postgresql/9.3/main/postgresql.conf'
 
 
 
 So what are the port numbers in the conf files.
 
 I have not used that option, but is there a chance you are double
 starting the servers?
 
 Starting an instance for each using the port numbers from the conf
 files and then pg_upgrade is starting them on 50432.
 
 

Sorry guys, one of the in-site tech support guys started the postgresql
service while we were trying to do the upgrade. Looks like this was the
cause of the failure.

Regards,

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Pagination count strategies

[Leonardo M . Ramé]

Hi, in one of our systems, we added a kind of pagination feature, that
shows N records of Total records.

To do this, we added a count(*) over() as Total field in our queries
in replacement of doing two queries, one for fetching the records, and
other for getting the count. This improved the performance, but we are't
happy with the results yet, by removing the count, the query takes
200ms vs 2000ms with it.

We are thinking of removing the count, but if we do that, the system
will lack an interesting feature.

What strategy for showing the total number of records returned do you
recommend?.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] Pagination count strategies

[Leonardo M . Ramé]

On 2014-04-03 10:00:18 -0400, Andrew Sullivan wrote:
 On Thu, Apr 03, 2014 at 10:34:32AM -0300, Leonardo M. Ramé wrote:
  
  What strategy for showing the total number of records returned do you
  recommend?.
 
 The best answer for this I've ever seen is to limit the number of rows
 you're counting (at least at first) to some reasonably small number --
 say 5000.  This is usually reasonably fast for a well-indexed query,
 and your pagination can say something like First n of at least 5000
 results, unless you have fewer than 5000 results, in which case you
 know the number (and the count returned quickly anyway).  As you're
 displaying those first 5000 results, you can work in the background
 getting a more accurate number.  This is more work for your
 application, but it provides a much better user experience (and you
 can delay getting the detailed number until the user pages through to
 the second page of results, so you don't count everything needlessly
 in case the user just uses the first page, which IME happens a lot).
 Note that even Google doesn't give you an accurate number -- they just
 say about ten trillion or whatever.
 
 Hope that's useful,
 
 A
 

Sounds nice, is it possible to modify my count(*) over() to what you
suggest?.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] Pagination count strategies

[Leonardo M . Ramé]

On 2014-04-03 17:19:56 +0200, Torsten Förtsch wrote:
 On 03/04/14 15:34, Leonardo M. Ramé wrote:
  Hi, in one of our systems, we added a kind of pagination feature, that
  shows N records of Total records.
  
  To do this, we added a count(*) over() as Total field in our queries
  in replacement of doing two queries, one for fetching the records, and
  other for getting the count. This improved the performance, but we are't
  happy with the results yet, by removing the count, the query takes
  200ms vs 2000ms with it.
  
  We are thinking of removing the count, but if we do that, the system
  will lack an interesting feature.
  
  What strategy for showing the total number of records returned do you
  recommend?.
 
 If you need only an estimated number and if your planner statistics are
 up to date, you can use the planner.
 
 Here is my implementation of the explain function. The COMMENT below
 shows how to use it:
 
 CREATE OR REPLACE FUNCTION explain(VARIADIC TEXT[])
 RETURNS JSON AS $$
 DECLARE
 tmp TEXT;
 BEGIN
 EXECUTE 'EXPLAIN ('
  || array_to_string(array_append($1[2:array_upper($1, 1)],
 'FORMAT JSON'), ', ')
  || ') '
  || $1[1] INTO tmp;
 RETURN tmp::JSON;
 END;
 $$ LANGUAGE plpgsql VOLATILE;
 
 COMMENT ON FUNCTION explain(VARIADIC TEXT[])
 IS $def$
 This function is a SQL interface to the planner. It returns the plan
 (result of EXPLAIN) of the query passed as TEXT string as the first
 parameter as JSON object.
 
 The remaining parameters are EXPLAIN-modifiers, like ANALYZE or
 BUFFERS.
 
 The function can be used to store plans in the database.
 
 Another interesting usage is when you need only an estimated row
 count for a query. You can use
 
   SELECT count(*) ...
 
 This gives you an exact number but is usually slow. If your planner
 statistics are up to date and the query is not too complicated, the
 planner usually gives a good estimate and is much faster.
 
   SELECT explain('SELECT 1 FROM tb WHERE id8000')
-0-'Plan'-'Plan Rows';
 $def$;
 
 
 Torsten

Nice!, do you know if this will work on 8.4?.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] Complex query

[Leonardo M . Ramé]

On 2014-03-31 18:48:58 +, Igor Neyman wrote:
  -Original Message-
  From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
  ow...@postgresql.org] On Behalf Of Leonardo M. Ramé
  Sent: Monday, March 31, 2014 2:38 PM
  To: PostgreSql-general
  Subject: [GENERAL] Complex query
  
  Hi, I'm looking for help with this query.
  
 
 Leonardo,
 
 Unless you add one more column to your Tasks table, specifically: 
 StatusTimestamp as in:
 
 IdTask  StatusCode  StatusName StatusTimestamp
 
 You cannot find which record in the table follows which, because order in 
 which records returned from the database is not guaranteed until you add 
 ORDER BY clause to your SELECT statement.
 
 Regards,
 Igor Neyman
  
 

You are right, let's add the Id column. This is just an example, the
real table (a view) contains both, the Id and a timestamp:

Id IdTask  StatusCode  StatusName
--
1  1   R   Registered
2  1   S   Started
3  1   D   Dictated
4  1   F   Finished
5  1   T   Transcribed
--
6  2   R   Registered
7  2   S   Started
8  2   T   Transcribed
9  2   F   Finished

After adding the Id column, can I use a window function to get what I
need?.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Complex query

[Leonardo M . Ramé]

Hi, I'm looking for help with this query. 

Table Tasks:

IdTask  StatusCode  StatusName
--
1   R   Registered
1   S   Started
1   D   Dictated
1   F   Finished
1   T   Transcribed
--
2   R   Registered
2   S   Started
2   T   Transcribed
2   F   Finished

As you can see, I have a table containing tasks and statuses. What I
would like to get is the list of tasks, including all of its steps, for
only those tasks where the StatusCode sequence was S followed by T.

In this example, the query should only return task Nº 2:

2   R   Registered
2   S   Started
2   T   Transcribed
2   F   Finished

Can anybody help me with this?.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] Complex query

[Leonardo M . Ramé]

On 2014-03-31 11:46:28 -0700, David Johnston wrote:
 Leonardo M. Ramé-2 wrote
  Hi, I'm looking for help with this query. 
  
  Table Tasks:
  
  IdTask  StatusCode  StatusName
  --
  1   R   Registered
  1   S   Started
  1   D   Dictated
  1   F   Finished
  1   T   Transcribed
  --
  2   R   Registered
  2   S   Started
  2   T   Transcribed
  2   F   Finished
  
  As you can see, I have a table containing tasks and statuses. What I
  would like to get is the list of tasks, including all of its steps, for
  only those tasks where the StatusCode sequence was S followed by T.
  
  In this example, the query should only return task Nº 2:
  
  2   R   Registered
  2   S   Started
  2   T   Transcribed
  2   F   Finished
  
  Can anybody help me with this?.
 
 First you need to decide how tell the database that R-S-T-F is ordered and
 then maybe you can use window functions, specifically lag(col, -1) over
 (...), to determine what the prior row's code is and act accordingly.
 
 Put that into a sub-query and return the IdTask to the outer query's where
 clause.
 
 David J.
 
 
Thanks David, I hope I understood what you mean.

After adding the Id column, I came up with this query:

ris=# select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, 
id, lag(code, -1) over () as lg  from tasks_test) as lag;
 id | idtask | code | lg 
++--+
  1 |  1 | R| S
  2 |  1 | S| D
  3 |  1 | D| F
  4 |  1 | F| T
  5 |  1 | T| R
  6 |  2 | R| S
  7 |  2 | S| T
  8 |  2 | T| F
  9 |  2 | F| 
(9 rows)

Row nº 7 meets the condition, but I don't want to show only that row, I
would like to show this:

  6 |  2 | R| S
  7 |  2 | S| T
  8 |  2 | T| F
  9 |  2 | F| 

Any hint?.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] Complex query

[Leonardo M . Ramé]

On 2014-03-31 12:16:53 -0700, David Johnston wrote:
 Leonardo M. Ramé-2 wrote
  select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, id,
  lag(code, -1) over () as lg  from tasks_test) as lag
 
 First you want to include an ORDER BY in the OVER(...) clause, and probably
 a PARTITION BY as well.
 
 Then you move that to a sub-query (for example):
 
 SELECT * 
 FROM tbl
 WHERE tbl.idtask IN (
 SELECT lag.idtask FROM ( lag_query_here ) lag WHERE lag.code = 'T' and
 lag.lg = 'S'
 );
 
 David J.
 

Great!, that's what I needed, thank you.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Determine Client Encoding

[Leonardo M . Ramé]

Hi, we found characters with different enconding in our database. As our
system is accessed by many PCs I would like to know if it's possible to
know the encoding of each connection, without going to each PC to check
its connection string.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] Determine Client Encoding

[Leonardo M . Ramé]

On 2014-02-26 09:48:12 -0500, Tom Lane wrote:
 Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= l.r...@griensu.com writes:
  Hi, we found characters with different enconding in our database. As our
  system is accessed by many PCs I would like to know if it's possible to
  know the encoding of each connection, without going to each PC to check
  its connection string.
 
 If you mean can one session identify the client_encoding of another
 session, no; that information isn't exposed anyplace.  Within a session,
 you can of course use show client_encoding or various equivalent
 syntaxes.
 
 Note that when you have encoding problems, as often as not the issue
 is that the data the client is sending isn't really in the encoding
 its client_encoding setting claims.  So even if you could find that
 out remotely, it probably wouldn't help localize the issue very well.
 
   regards, tom lane

Thanks Tom, let me try to understand what you said.

For example if client_encoding is set to win1252, but the user does a
copy-paste from MsWord (usually they do this), characters could have
been sent in utf8 ?.

If this is the case, the insert/update is done, but cannot be read from
another client. Right?.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump/pg_restore issues

[Leonardo M . Ramé]

Hi, I'm backing up a big database using the --exclude-table option for
two tables, say table1 and table2. Then another backup of only those
tables, so, the final result are three backup files.

basic.backup
table1.backup
table2.backup

The problem I'm facing is at the restore moment is that basic.backup
contains view definitions related to table1 or table2, hence, the
restore does not create those views.

How do you recommend to workaround this?.

P.S.: I create three files because table1 and table2 are tables with
blob data, and we use basic.backup to create testing database where we
don't need blob data.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] pg_dump/pg_restore issues

[Leonardo M . Ramé]

On 2014-02-19 10:08:19 -0800, bricklen wrote:
 On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé l.r...@griensu.comwrote:
 
  Hi, I'm backing up a big database using the --exclude-table option for
  two tables, say table1 and table2. Then another backup of only those
  tables, so, the final result are three backup files.
 
  basic.backup
  table1.backup
  table2.backup
 
  The problem I'm facing is at the restore moment is that basic.backup
  contains view definitions related to table1 or table2, hence, the
  restore does not create those views.
 
  How do you recommend to workaround this?.
 
  P.S.: I create three files because table1 and table2 are tables with
  blob data, and we use basic.backup to create testing database where we
  don't need blob data.
 
 
 The --section option of pg_dump might allow you dump the views separately.
 Alternatively, if you know the names of the views that will fail, you could
 pg_dump as you are doing now, but in custom format (-Fc), then use
 pg_restore to create a list file from the contents, comment out the views,
 pg_restore using the list file (minus those views), then pg_dump using
 another list file with *only* those views.

The good news are that I'm using -Fc, now I'll generate the list. I've
found there's a sequence related to one of those tables and I'm
wondering if there's a way to backup the sequence only.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] pg_dump/pg_restore issues

[Leonardo M . Ramé]

On 2014-02-19 10:23:58 -0800, Adrian Klaver wrote:
 On 02/19/2014 10:08 AM, bricklen wrote:
 
 On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé
 l.r...@griensu.com mailto:l.r...@griensu.com wrote:
 
 Hi, I'm backing up a big database using the --exclude-table option for
 two tables, say table1 and table2. Then another backup of only those
 tables, so, the final result are three backup files.
 
 basic.backup
 table1.backup
 table2.backup
 
 The problem I'm facing is at the restore moment is that basic.backup
 contains view definitions related to table1 or table2, hence, the
 restore does not create those views.
 
 How do you recommend to workaround this?.
 
 P.S.: I create three files because table1 and table2 are tables with
 blob data, and we use basic.backup to create testing database where we
 don't need blob data.
 
 
 The --section option of pg_dump might allow you dump the views separately.
 Alternatively, if you know the names of the views that will fail,
 you could pg_dump as you are doing now, but in custom format
 (-Fc), then use pg_restore to create a list file from the
 contents, comment out the views, pg_restore using the list file
 (minus those views), then pg_dump using another list file with
 *only* those views.
 Another alternative would be to add another backup:
 
 pg_dump -s -t table1 -t table2 -f view.dump
 
 This will dump the table definitions only which is all you need.
 
 And then in order restore:
 
 view.dump
 basic.backup

That makes sense, I'll try it.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_restore issue

[Leonardo M . Ramé]

Hi, I'm trying to restore a database dump using pg_restore with the
following parameters:

pg_restore -h 127.0.0.1 -U _postgresql \
  -c -d postgres --exit-on-error \
  my_dump.backup

Note I used \ to wrap the command, but the real one does not have
those.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
CONSTRAINT fkidturno postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
public.turnodocumento does not exist
Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
fkidturno;

Why is this happening?, should I change the command I'm using to create
the backup?.

To backup the database I'm using:

pg_dump -Fc -h 127.0.0.1 -U postgres mydb  my_dump.backup

P.S.: the original db is PostgreSql 8.4, the target is 9.2

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] pg_restore issue

[Leonardo M . Ramé]

On 2014-02-12 09:51:10 -0800, Adrian Klaver wrote:
 On 02/12/2014 09:41 AM, Leonardo M. Ramé wrote:
 Hi, I'm trying to restore a database dump using pg_restore with the
 following parameters:
 
 pg_restore -h 127.0.0.1 -U _postgresql \
-c -d postgres --exit-on-error \
my_dump.backup
 
 Note I used \ to wrap the command, but the real one does not have
 those.
 
 pg_restore: [archiver (db)] Error while PROCESSING TOC:
 pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
 CONSTRAINT fkidturno postgres
 pg_restore: [archiver (db)] could not execute query: ERROR:  relation
 public.turnodocumento does not exist
  Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
 fkidturno;
 
 Why is this happening?, should I change the command I'm using to create
 the backup?.
 
 Did you look in the restored database to see if everything is
 correct or not?
 
 
 What version of pg_dump did you use to do the pg_dump, the 8.4 or 9.2 one?
 
 It is recommended that you use the later version to dump older
 databases as it can deal with any changes that have occurred.
 
 
 To backup the database I'm using:
 
 pg_dump -Fc -h 127.0.0.1 -U postgres mydb  my_dump.backup
 
 P.S.: the original db is PostgreSql 8.4, the target is 9.2
 
 Regards,
 
 

Thanks Adrian, but I cannot use a newer pg_dump version because I cannot
upgrade it inside the remote server, also, as the db is very large I
prefer to do the backup internally and rsync the file.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] pg_restore issue

[Leonardo M . Ramé]

On 2014-02-12 13:30:52 -0500, Tom Lane wrote:
 Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= l.r...@griensu.com writes:
  Hi, I'm trying to restore a database dump using pg_restore with the
  following parameters:
 
  pg_restore -h 127.0.0.1 -U _postgresql \
-c -d postgres --exit-on-error \
my_dump.backup
 
  Note I used \ to wrap the command, but the real one does not have
  those.
 
  pg_restore: [archiver (db)] Error while PROCESSING TOC:
  pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
  CONSTRAINT fkidturno postgres
  pg_restore: [archiver (db)] could not execute query: ERROR:  relation
  public.turnodocumento does not exist
  Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
  fkidturno;
 
  Why is this happening?, should I change the command I'm using to create
  the backup?.
 
 The -c switch causes pg_restore to try to DROP every object it's about to
 restore.  If you're restoring into an empty database then this is useless,
 and in fact will not work if you're also using --exit-on-error.  Remove
 one or the other switch.
 
 Now, if you expected that all the objects do exist in the target database,
 then it might be worth inquiring a bit more closely into what's happening.
 
   regards, tom lane

Tom, I've dropped the db, then createdb again, then removed the -c
option, now I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 735; 2612 213488
PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  language
plpgsql already exists
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;


-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
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] pg_restore issue

[Leonardo M . Ramé]

On 2014-02-12 14:04:41 -0500, Tom Lane wrote:
 Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= l.r...@griensu.com writes:
  On 2014-02-12 13:30:52 -0500, Tom Lane wrote:
  The -c switch causes pg_restore to try to DROP every object it's about to
  restore.  If you're restoring into an empty database then this is useless,
  and in fact will not work if you're also using --exit-on-error.  Remove
  one or the other switch.
 
  Tom, I've dropped the db, then createdb again, then removed the -c
  option, now I get this error:
 
  pg_restore: [archiver (db)] Error while PROCESSING TOC:
  pg_restore: [archiver (db)] Error from TOC entry 735; 2612 213488
  PROCEDURAL LANGUAGE plpgsql postgres
  pg_restore: [archiver (db)] could not execute query: ERROR:  language
  plpgsql already exists
  Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
 
 pg_dump versions more recent than 8.4 use CREATE OR REPLACE PROCEDURAL
 LANGUAGE to work around the possibility that plpgsql is preinstalled.
 If you don't want to use a modern pg_dump, you'll need to not use
 --exit-on-error.
 
 In general, it's recommended to use the newer pg_dump when trying to
 transfer data from an older installation to a newer one.  You can
 generally make it work without that, but it's not necessarily going
 to be seamless, and one of the ways it tends to not be seamless is
 that you have to be willing to ignore harmless errors.
 

Ok, I understand your reasoning. Removing -c and --exit-on-error fixed
the issue.

BTW, I've used --exit-on-error because there were many errors, and I wanted
to fix each one of them. 

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Filtering queries by IP

[Leonardo M . Ramé]

Hi, I'm trying to find the cause of slow performance on some screens of
an application. To do that, I would like to be able to log all the
queries made by an specific IP addres, is this possible?.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Londiste3 (SkyTools3)

[Leonardo M . Ramé]

Hi, I've installed SkyTools3 on Ubuntu 12.04 Server, and got stuck when
trying to execute pgqadm.py (Step 5:
http://manojadinesh.blogspot.com.ar/2012/11/skytools-londiste-replication.html).

Does anyone know if pgqadm.py was replaced in SkyTools3?. Any up-to-date
tutorial?.

Regards,
-- 
Leonardo M. Ramé
http://leonardorame.blogspot.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] AutoVacuum Daemon

[Leonardo M . Ramé]

Hi, I want know if I should run the auto-vacuum daemon (from
/etc/init.d/) or it runs automatically and transparently if configured
in postgres.conf?. If it must be configured manually, what is the script
to be run, I didn't find pg_autovacuum or similar. 

I didn't find information about this on this page:

http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#AUTOVACUUM

P.S.: I'm on linux running PostgreSql 8.4

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] AutoVacuum Daemon

[Leonardo M . Ramé]

On 2013-12-30 13:45:43 +, Haribabu kommi wrote:
 On 30 December 2013 19:11 Leonardo M. Ramé wrote:
  Hi, I want know if I should run the auto-vacuum daemon (from
  /etc/init.d/) or it runs automatically and transparently if configured
  in postgres.conf?. If it must be configured manually, what is the
  script to be run, I didn't find pg_autovacuum or similar.
  
  I didn't find information about this on this page:
  
  http://www.postgresql.org/docs/8.4/static/routine-
  vacuuming.html#AUTOVACUUM
  
  P.S.: I'm on linux running PostgreSql 8.4
 
 Just enable autovacuum configuration parameter in postgresql.conf file.
 Which internally spawns an autovacuum process which will take care of 
 vacuuming.
 

Thanks, that's easier than I thought.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Alter table never finishes

[Leonardo M . Ramé]

Hi, I need to do an alter table on a small table (~300 records), but it
never ends. It may be because there are clients using that table.

How can I force disconnect all clients to let me alter that table?.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Alter table never finishes

[Leonardo M . Ramé]

On 2013-07-30 17:56:16 +0200, Giuseppe Broccolo wrote:
 How can I force disconnect all clients to let me alter that table?.
 
 Regards,
 
 There are two ways: the first|is based on pg_terminate_backend()
 function and 'pg_stat_activity' catalog |||to kill idle processes.
 So in a psql session type (tried on PostgreSQL 8.4):
 
 ==# SELECT procpid, (SELECT pg_terminate_backend(procid)) AS killed
 from pg_stat_activity WHERE current_query LIKE 'IDLE';
 
 A more heavy handed approach then should be used on terminal,
 forcing kill of idle processes using their pid:
 
 :$ for x in `ps -ef | grep -e postgres.*idle | awk '{print $2}'`;
 do kill -9 $x; done
 
 Hope it can help.
 
 Giuseppe.
 
 -- 
 Giuseppe Broccolo - 2ndQuadrant Italy
 PostgreSQL Training, Services and Support
 giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
 

Thanks to both, Giuseppe and Bricklen. As I have 9.2 I've used:

select pg_cancel_backend(pid) from pg_stat_activity where pid  
pg_backend_pid();

And it returned this:

pg_cancel_backend
---
 t
 t
(2 rows)

But when I execute my update table command, it still never ends...Any
hint?.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Alter table never finishes

[Leonardo M . Ramé]

On 2013-07-30 10:26:39 -0700, bricklen wrote:
 On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé l.r...@griensu.comwrote:
 
 
  select pg_cancel_backend(pid) from pg_stat_activity where pid 
  pg_backend_pid();
 
  And it returned this:
 
  pg_cancel_backend
  ---
   t
   t
  (2 rows)
 
  But when I execute my update table command, it still never ends...Any
  hint?.
 
 
 Sounds like locking issues. In another session -- other than the one you
 are trying to run your update, what does the following query show?
 
 SELECT
 waiting.locktype   AS waiting_locktype,
 waiting.relation::regclass AS waiting_table,
 waiting_stm.query  AS waiting_query,
 waiting.mode   AS waiting_mode,
 waiting.pidAS waiting_pid,
 other.locktype AS other_locktype,
 other.relation::regclass   AS other_table,
 other_stm.queryAS other_query,
 other.mode AS other_mode,
 other.pid  AS other_pid,
 other.granted  AS other_granted
 FROM pg_catalog.pg_locks AS waiting
 JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.pid =
 waiting.pid)
 JOIN pg_catalog.pg_locks AS other ON ((waiting.database =
 other.database AND waiting.relation  = other.relation) OR
 waiting.transactionid = other.transactionid)
 JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid)
 WHERE NOT waiting.granted
 AND waiting.pid  other.pid;

Sorry bricklen, I've killed all idle connections with kill -9 PID,
then I was able to execute the alter table.


-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Spellcheck function

[Leonardo M . Ramé]

Hi, I need to implement a spell cheker for a client-server application
with a shared dictionary.

I was thinking of implementing it as a Postgres function, but, before
that I would like to know if it or something similar is already
implemented.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Parsing COPY ... WITH BINARY

[Leonardo M . Ramé]

On 2013-01-31 17:38:26 -0600, Merlin Moncure wrote:
 On Thu, Jan 31, 2013 at 8:47 AM, Leonardo M. Ramé l.r...@griensu.com wrote:
  I'm using this:
 
  COPY( select field1, field2, field3 from table ) TO 'C://Program 
  Files/PostgreSql//8.4//data//output.dat' WITH BINARY
 
  To export some fields to a file, one of them is a ByteA field. Now, I
  need to read the file with a custom made program.
 
  How can I parse this file?
 
  BTW: I cannot export as CSV, because the binary data is modified when I
  do that.
 
 this type of thing is probably better handled with libpq based C
 application if you're willing to write one.  all this is assuming you
 don't want to decode your bytea from encoded format such as hex.  why
 do you specifically need to dump in binary?
 
 merlin
 

I need to dump in binary because in the dump I have bytea fields. I know
PostgreSql escapes binary data, but I don't want to regenerate it to
see in my program.

Anyway, I solved the issue by creating a FreePascal program for parsing
the file, based on the info from here:
http://www.postgresql.org/docs/current/static/sql-copy.html#AEN66736

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Parsing COPY ... WITH BINARY

[Leonardo M . Ramé]

I'm using this:

COPY( select field1, field2, field3 from table ) TO 'C://Program 
Files/PostgreSql//8.4//data//output.dat' WITH BINARY

To export some fields to a file, one of them is a ByteA field. Now, I
need to read the file with a custom made program.

How can I parse this file?

BTW: I cannot export as CSV, because the binary data is modified when I
do that.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump problem

[Leonardo M . Ramé]

I'm trying to migrate a PostgreSql 8.3 database from a Windows 2003
server to a PostgreSql 8.4 Linux x86_64 server running Ubuntu Server
12.04.

When running pg_dump from the Linux server, I get:

postgres@ubuntupostgresql:~$ pg_dump -h 192.168.10.105 -U postgres ris
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  unrecognized configuration 
parameter synchronize_seqscans
pg_dump: The command was: SET synchronize_seqscans TO off

I've read in older mails that this massage shows when one side is
running EnterpriseDB version of PostgreSql and the other is running a
PostgreSql version. I must point out this is not my case, both sides are
running PostgreSql versions, the Linux side is running the one from the
official repository, and the Windows one is running an old 8.3 version
downloaded from postgresql.org.

Any hint?.


Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrading 8.3-rc1 to 8.3.20

[Leonardo M . Ramé]

I want to upgrade an 8.3-rc1 to 8.3.20 Win32 server (I cannot upgrade to
8.4 nor 9.1 because the client app doesn't support them). The question
is, is there a tutorial, or a step-by-step guide to to this?.

Can I just decompress the postgresql-8.3.20-1-binaries-no-installer.zip
file and just overwrite the 8.3-rc1 directory?.

Regards, 
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Upgrading 8.3-rc1 to 8.3.20

[Leonardo M . Ramé]

On 2012-10-03 16:51:59 +0200, Andreas Kretschmer wrote:
 Raymond O'Donnell r...@iol.ie wrote:
 
  On 03/10/2012 15:21, Leonardo M. Ramé wrote:
   I want to upgrade an 8.3-rc1 to 8.3.20 Win32 server (I cannot upgrade to
   8.4 nor 9.1 because the client app doesn't support them). The question
   is, is there a tutorial, or a step-by-step guide to to this?.
  
  The usual way is to pg_dump the old one first, then restore it
  afterwards into the new installation. I don't know, however, if you need
  to do this in your case - generally you don't when moving to a minor
  release of the same major version... the release notes for 8.3.0 should
  say, I'd imagine.
 
 He is using an RC-Release, so i'm not sure, but i think, he have to go
 the long way (Backup  restore) and he can't go the short way (Minor
 release update)
 
 
 Andreas
 -- 
 Really, I'm not out to destroy Microsoft. That will just be a completely
 unintentional side effect.  (Linus Torvalds)
 If I was god, I would recompile penguin with --enable-fly.   (unknown)
 Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Thanks, I'll go the long way...

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Linux PowerPC 64bits issue

[Leonardo M . Ramé]

On 2012-09-27 11:51:46 +0800, a...@hsk.hk wrote:
 Hi,
 
 Could you provide the steps and commands you used to compile 9.1?
 
 I want to reproduce your case in my machine
 
 Regards
 Arthur
 

Yes, I downloaded postgresql-9.1.5.tar.gz from the PostgreSql web site,
then did this:

tar xvfz postgresql-9.1.5.tar.gz
cd postgresql-9.1.5
./configure
make
make install

This, apart from copying the server files, created the
/usr/local/pgsql/lib directory, where I have libpq.so and many more
shared libraries.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Linux PowerPC 64bits issue

[Leonardo M . Ramé]

On 2012-09-27 21:44:21 +0800, a...@hsk.hk wrote:
 Hi,
 
 I have tried to compile it in my local machine, I could not reproduce the 
 issue yet.
 
 Does anyone else have idea what the reason would be? Would the issue come 
 from the config file? 
 
 Regards
 Arthur 
  
 On 27 Sep 2012, at 7:46 PM, Leonardo M. Ramé wrote:
 
  On 2012-09-27 11:51:46 +0800, a...@hsk.hk wrote:
  Hi,
  
  Could you provide the steps and commands you used to compile 9.1?
  
  I want to reproduce your case in my machine
  
  Regards
  Arthur
  
  
  Yes, I downloaded postgresql-9.1.5.tar.gz from the PostgreSql web site,
  then did this:
  
  tar xvfz postgresql-9.1.5.tar.gz
  cd postgresql-9.1.5
  ./configure
  make
  make install
  
  This, apart from copying the server files, created the
  /usr/local/pgsql/lib directory, where I have libpq.so and many more
  shared libraries.
  
  -- 
  Leonardo M. Ramé
  Medical IT - Griensu S.A.
  Av. Colón 636 - Piso 8 Of. A
  X5000EPT -- Córdoba
  Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
  Cel.: +54 9 351 6629292
  
 

Arthur, could you post the results of ldd
/usr/local/pgsql/lib/libpq.so ?.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Linux PowerPC 64bits issue

[Leonardo M . Ramé]

On 2012-09-27 06:53:57 -0700, Adrian Klaver wrote:
 On 09/27/2012 04:46 AM, Leonardo M. Ramé wrote:
 On 2012-09-27 11:51:46 +0800, a...@hsk.hk wrote:
 Hi,
 
 Could you provide the steps and commands you used to compile 9.1?
 
 I want to reproduce your case in my machine
 
 Regards
 Arthur
 
 
 Yes, I downloaded postgresql-9.1.5.tar.gz from the PostgreSql web site,
 then did this:
 
 tar xvfz postgresql-9.1.5.tar.gz
 cd postgresql-9.1.5
 ./configure
 make
 make install
 
 By any chance did you in the past install a Postgres RPM that may
 introduced 32 bit libraries that the source is linking against?
 

Well, this is an inherited server, so everything is possible. Let me
check.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Linux PowerPC 64bits issue

[Leonardo M . Ramé]

On 2012-09-27 06:53:57 -0700, Adrian Klaver wrote:
 On 09/27/2012 04:46 AM, Leonardo M. Ramé wrote:
 On 2012-09-27 11:51:46 +0800, a...@hsk.hk wrote:
 Hi,
 
 Could you provide the steps and commands you used to compile 9.1?
 
 I want to reproduce your case in my machine
 
 Regards
 Arthur
 
 
 Yes, I downloaded postgresql-9.1.5.tar.gz from the PostgreSql web site,
 then did this:
 
 tar xvfz postgresql-9.1.5.tar.gz
 cd postgresql-9.1.5
 ./configure
 make
 make install
 
 By any chance did you in the past install a Postgres RPM that may
 introduced 32 bit libraries that the source is linking against?
 

Is it possible to force linking against only to libraries residing in my
/usr/lib64 ?.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Linux PowerPC 64bits issue

[Leonardo M . Ramé]

On 2012-09-27 11:02:48 -0300, Leonardo M. Ramé wrote:
 On 2012-09-27 06:53:57 -0700, Adrian Klaver wrote:
  On 09/27/2012 04:46 AM, Leonardo M. Ramé wrote:
  On 2012-09-27 11:51:46 +0800, a...@hsk.hk wrote:
  Hi,
  
  Could you provide the steps and commands you used to compile 9.1?
  
  I want to reproduce your case in my machine
  
  Regards
  Arthur
  
  
  Yes, I downloaded postgresql-9.1.5.tar.gz from the PostgreSql web site,
  then did this:
  
  tar xvfz postgresql-9.1.5.tar.gz
  cd postgresql-9.1.5
  ./configure
  make
  make install
  
  By any chance did you in the past install a Postgres RPM that may
  introduced 32 bit libraries that the source is linking against?
  
 
 Is it possible to force linking against only to libraries residing in my
 /usr/lib64 ?.
 
 -- 
 Leonardo M. Ramé
 Medical IT - Griensu S.A.
 Av. Colón 636 - Piso 8 Of. A
 X5000EPT -- Córdoba
 Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
 Cel.: +54 9 351 6629292
 
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

I'd executed this command to remove all 32 bit packages from the system
(remember this is an OpenSuse system):

rpm -qa --qf '%{name}.%{arch}\n' | grep 'i[36]86$' | xargs rpm -e

But it returned rpm: no packages given for erase. So, there shouldn't
be 32bit libraries in the system.


Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
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] Linux PowerPC 64bits issue

[Leonardo M . Ramé]

On 2012-09-27 11:21:04 -0300, Leonardo M. Ramé wrote:
 On 2012-09-27 11:02:48 -0300, Leonardo M. Ramé wrote:
  On 2012-09-27 06:53:57 -0700, Adrian Klaver wrote:
   On 09/27/2012 04:46 AM, Leonardo M. Ramé wrote:
   On 2012-09-27 11:51:46 +0800, a...@hsk.hk wrote:
   Hi,
   
   Could you provide the steps and commands you used to compile 9.1?
   
   I want to reproduce your case in my machine
   
   Regards
   Arthur
   
   
   Yes, I downloaded postgresql-9.1.5.tar.gz from the PostgreSql web site,
   then did this:
   
   tar xvfz postgresql-9.1.5.tar.gz
   cd postgresql-9.1.5
   ./configure
   make
   make install
   
   By any chance did you in the past install a Postgres RPM that may
   introduced 32 bit libraries that the source is linking against?
   
  
  Is it possible to force linking against only to libraries residing in my
  /usr/lib64 ?.
  
  -- 
  Leonardo M. Ramé
  Medical IT - Griensu S.A.
  Av. Colón 636 - Piso 8 Of. A
  X5000EPT -- Córdoba
  Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
  Cel.: +54 9 351 6629292
  
  
  
  -- 
  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-general
 
 I'd executed this command to remove all 32 bit packages from the system
 (remember this is an OpenSuse system):
 
 rpm -qa --qf '%{name}.%{arch}\n' | grep 'i[36]86$' | xargs rpm -e
 
 But it returned rpm: no packages given for erase. So, there shouldn't
 be 32bit libraries in the system.
 
 

I've found the culpit:

:#ldd /lib/libpthread-2.4.so
linux-vdso32.so.1 =  (0x0010)
libc.so.6 = /lib/power6x/libc.so.6 (0x07e8c000)
/lib/ld.so.1 (0xf7fe1000)

Now I must find out how to install libpthread for 64 bits.

-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Linux PowerPC 64bits issue

[Leonardo M . Ramé]

Hi, I compiled PostgreSql 9.1 from sources in a OpenSuse 10.1 PowerPC
machine. While trying to test one application, I've got errors just
before connecting to the database, and found my app is loading
linux-vdso64.so.1 while libpq.so uses linux-vdso32.so.1

This means the PostgreSql libraries where compiled in 32 bits?. This is
what ldd gives:

ldd /usr/local/pgsql/lib/libpq.so
linux-vdso32.so.1 = (0x0010)
libpthread.so.0 = /lib/power6x/libpthread.so.0 (0x6ff85000)
libc.so.6 = /lib/power6x/libc.so.6 (0x6fe11000)
/lib/ld.so.1 (0x0800)

How can I force PostgreSql to compile in 64 bits?.

Regards,
-- 
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general