Re: [GENERAL] Re: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[David Johnston]

On May 3, 2012, at 20:20, EllyR  wrote:

> Yes you are right, I have read that it is not that reliable, but I have to do
> something for that, what would be your suggestion for this case? Thanks.

Simplest option: install a new local hard drive and configure a tablespace to 
use it.

Alternative: Buy and configure a new machine then dump/restore your database to 
it.

Not enough info on resources and requirements to recommend one over the other 
or to devise additional alternatives.

David J.


-- 
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] Re: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[John R Pierce]

On 05/03/12 5:15 PM, EllyR wrote:

I have the latest Postgres on my machine, do you know where can I find the
instruction for map network drive? or do you think it won't work at all on
network drive?


database servers should not be run in SMB/CIFS network shares.   The 
shares just aren't reliable at things like synchronous commit, and 
random small block writes, that database servers do a LOT of.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] trimming a column

[Michael P. Soulier]

On 03/05/12 Michael P. Soulier said:

> lots and lots of whitespace. I think it's in the db.

Ah, they're not varchars, they're character columns. That explains it...


signature.asc
Description: Digital signature

[GENERAL] Re: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[EllyR]

Thanks for answering,

I have the latest Postgres on my machine, do you know where can I find the
instruction for map network drive? or do you think it won't work at all on
network drive? 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058p5684608.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Re: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[EllyR]

One I tried to install it on map network drive, it failed to work, so I had
to install it back on the C drive.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058p5684612.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Re: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[EllyR]

Yes you are right, I have read that it is not that reliable, but I have to do
something for that, what would be your suggestion for this case? Thanks.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058p5684620.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] trimming a column

[Michael P. Soulier]

On 03/05/12 Michael P. Soulier said:

> still, in my app I see a lot of trailing whitespace after reading from the
> db...

[root@vmbg-msoulier3 eventviewer]# PYTHONPATH=.. python manage.py shell
Python 2.6.6 (r266:84292, Dec  7 2011, 20:38:36) 
[GCC 4.4.6 20110731 (Red Hat 4.4.6-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from main.models import *
>>> dir()
['ApplicationEvent', '__builtins__', 'datetime', 'models']
>>> events = ApplicationEvent.objects.all()
>>> for event in events:
...print "'%s'" % event.id
... 
'vmbg-msoulier3.nssg.mitel.com_1-1335549662-26268
'
'vmbg-msoulier3.nssg.mitel.com_1-1335549687-0
'
'tug-manage-ssl-1335549693-0
'
'vmbg-msoulier3.nssg.mitel.com_1-1335553128-344
'
'vmbg-msoulier3.nssg.mitel.com_1-1335553152-0
'
'tug-manage-ssl-1335553155-0
'
'vmbg-msoulier3.nssg.mitel.com_1-1336056097-0
'
'vmbg-msoulier3.nssg.mitel.com_1-1336058939-285
'
'vmbg-msoulier3.nssg.mitel.com_1-1336058940-0
'

lots and lots of whitespace. I think it's in the db.

Mike


signature.asc
Description: Digital signature

Re: [GENERAL] trimming a column

[Michael P. Soulier]

On 03/05/12 Adrian Klaver said:

> Works here:
> test=> SELECT version();
> version
> ---
>  PostgreSQL 9.0.7 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE
> Linux) 4.6.2, 32-bit

eventsdb=# select version();
  version   



 PostgreSQL 8.4.10 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.4.5
2011021
4 (Red Hat 4.4.5-6), 32-bit
(1 row)


Perhaps it how the fields are displayed...

eventsdb=# select length(id), id from application_events;
 length |  id   
   
+---
---
 48 | vmbg-msoulier3.nssg.mitel.com_1-1335549662-26268  
  
 44 | vmbg-msoulier3.nssg.mitel.com_1-1335549687-0  
  
 27 | tug-manage-ssl-1335549693-0   
  
 46 | vmbg-msoulier3.nssg.mitel.com_1-1335553128-344
  
 44 | vmbg-msoulier3.nssg.mitel.com_1-1335553152-0  
  
 27 | tug-manage-ssl-1335553155-0   
  
 44 | vmbg-msoulier3.nssg.mitel.com_1-1336056097-0  
  
 46 | vmbg-msoulier3.nssg.mitel.com_1-1336058939-285
  
 44 | vmbg-msoulier3.nssg.mitel.com_1-1336058940-0  
  
(9 rows)

eventsdb=# select trim(id) from application_events;
  btrim   
--
 vmbg-msoulier3.nssg.mitel.com_1-1335549662-26268
 vmbg-msoulier3.nssg.mitel.com_1-1335549687-0
 tug-manage-ssl-1335549693-0
 vmbg-msoulier3.nssg.mitel.com_1-1335553128-344
 vmbg-msoulier3.nssg.mitel.com_1-1335553152-0
 tug-manage-ssl-1335553155-0
 vmbg-msoulier3.nssg.mitel.com_1-1336056097-0
 vmbg-msoulier3.nssg.mitel.com_1-1336058939-285
 vmbg-msoulier3.nssg.mitel.com_1-1336058940-0
(9 rows)

still, in my app I see a lot of trailing whitespace after reading from the
db...

Mike


signature.asc
Description: Digital signature

Re: [GENERAL] SQL functions not being inlined

[Evan Martin]

Of course, it seems silly now to not have included "SET search_path FROM 
current" in my post, but I had no idea what was and wasn't critical - 
that was the whole problem. Wisdom begins with knowing the right 
questions to ask! Yes, I was looking at the same function - even checked 
SVN logs to make sure and it had that "SET" for a long time. Still have 
no idea what happened there.


Re documentation - yes, the source code is well-commented, but I 
wouldn't have known where to look in the code if you hadn't told me. As 
a user, I didn't even know how to tell whether the function was being 
inlined or not. The EXPLAIN output just said "Function scan" - but it's 
/always/ said that for me, so how was I to know what it /should/ say? 
What about a section under chapter 14 (Performance Tips) on functions, 
which explains:


1) Why a function might run slower than running the same query directly. 
(Not inlined, plan caching, other reasons?)

2) How to tell whether a function is being inlined.
3) Things that would prevent a function from being inlined. I understand 
that some of these can change and ideally the doc should note which are 
inherent limitations and which are current implementation restrictions.


I'm going to write this up as a blog post just for my own reference, but 
nobody else is going to find it there. I'd love to see it documented 
properly by someone who knows what they're talking about.


Regards,

Evan

On 4/05/2012 1:30 AM, Tom Lane wrote:

Evan Martin  writes:

Thanks, I went into that function, added log statements everywhere and
figured which check it's failing on:
!heap_attisnull(func_tuple, Anum_pg_proc_proconfig)
and it's because my real function had this at the end:
SET search_path FROM CURRENT;

Well, shame on you for omitting that critical detail from your example.


which I never imagined would make any difference.

The reason SET options disable inlining is that inlining would leave
noplace for the SET to be applied and then removed.  In some cases it's
possible that we could prove that the SET need not occur at runtime,
but the inlining mechanism doesn't have that much knowledge about
configuration parameters.


This still doesn't
explain why it was being inlined sometimes - I didn't add and remove
that bit, it was there the whole time!

That does seem improbable.  You sure you were looking at just one
function?


Is there any reason this stuff isn't documented? It can have huge
performance implications, so I'm surprised more people don't run into
it.

There are a huge number of details of planner behavior that "can have
huge performance implications", and they change frequently.  It's not
clear to me that we can document this stuff in a way that's better than
referring to the source code.

regards, tom lane

Re: [GENERAL] trimming a column

[Adrian Klaver]

On 05/03/2012 06:01 PM, Michael P. Soulier wrote:

Hi,

I need to trim whitespace off of a whole column and replace the existing
values with the trimmed ones.

This isn't working

update mytable set id = trim(id);

I'm not sure of the correct syntax. Help appreciated.


Works here:
test=> SELECT version();
version 


---
 PostgreSQL 9.0.7 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE 
Linux) 4.6.2, 32-bit


test=> create table trim_test(fld_1 varchar);
CREATE TABLE
test=> INSERT INTO trim_test VALUES (' test ');
INSERT 0 1
test=> INSERT INTO trim_test VALUES (' test1 ');
INSERT 0 1
test=> INSERT INTO trim_test VALUES (' test2 ');
INSERT 0 1
test=> SELECT  length(fld_1), fld_1 from trim_test ;
 length |  fld_1
+-
  6 |  test
  7 |  test1
  7 |  test2
(3 rows)

test=> UPDATE trim_test set fld_1 = trim(fld_1);
UPDATE 3
test=> SELECT  length(fld_1), fld_1 from trim_test ;
 length | fld_1
+---
  4 | test
  5 | test1
  5 | test2


Sure you do not have an open transaction?
Say did the the UPDATE in one session inside a transaction without 
issuing a COMMIT and looking at data in another session that will not 
see changes until COMMIT is done.




Mike



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

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

Re: [GENERAL] trimming a column

[Adrian Klaver]

On 05/03/2012 06:55 PM, Greg Williamson wrote:

Michael --


Hi,

I need to trim whitespace off of a whole column and replace the existing
values with the trimmed ones.

This isn't working

update mytable set id = trim(id);

I'm not sure of the correct syntax. Help appreciated.

Mike



The trim function needs to be told what sort of trim to do --

Following the 9.1 manual (you did not specify which version of postgres you are 
using) try:

   UPDATE mytable SET id = trim(both ' ' from id).


Actually not:)
test=> SELECT  length(trim(' test '));
 length

  4



(untested)

See

HTH,

Greg Williamson




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

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

Re: [GENERAL] trimming a column

[Greg Williamson]

Michael --

>Hi,
>
>I need to trim whitespace off of a whole column and replace the existing
>values with the trimmed ones.
>
>This isn't working
>
>update mytable set id = trim(id);
>
>I'm not sure of the correct syntax. Help appreciated.
>
>Mike
>

The trim function needs to be told what sort of trim to do --

Following the 9.1 manual (you did not specify which version of postgres you are 
using) try:

  UPDATE mytable SET id = trim(both ' ' from id).

(untested)

See 

HTH,

Greg Williamson

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

[GENERAL] trimming a column

[Michael P. Soulier]

Hi,

I need to trim whitespace off of a whole column and replace the existing
values with the trimmed ones.

This isn't working

update mytable set id = trim(id);

I'm not sure of the correct syntax. Help appreciated.

Mike


signature.asc
Description: Digital signature

Re: [GENERAL] Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[Chris Angelico]

On Fri, May 4, 2012 at 4:55 AM, EllyR  wrote:
> I have a huge Postgres database on my C drive, I am continuously collecting
> the data, so unfortunately, the drive is getting full and I wanted to move
> it to a map network drive I got.

Are you able to install Postgres on a remote system and then use that
instead of running it locally? That would be far easier.

ChrisA

-- 
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] Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[Magnus Hagander]

On Thu, May 3, 2012 at 8:55 PM, EllyR  wrote:
> Hi,
>
> I have a huge Postgres database on my C drive, I am continuously collecting
> the data, so unfortunately, the drive is getting full and I wanted to move
> it to a map network drive I got. I followed the instruction in the link

It's not supported to run PostgreSQL off a windows network drive. It
might work, but it might also not - most likely it won't. There are
many different scenarios where this can cause corruption, and that's
why it's not supported.



> 4 - Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\pgsql-8.0

Note that PostgreSQL 8.0 is not supported, and hasn't been for years.
You need to upgrade to at least 8.3 to get a supported version.


> But after doing all the things mentioned in the link, when I want to start
> the postgres again as a service, it can not be started, it seems it does not
> recognize the map network drive , would you please let me know if I am in
> the right track or I need to do something else? any help is appreciated.
> Thanks.

As PostgreSQL runs as it's own service, it has it's own set of network
drives. You need to make that map in the profile of the postgres user
for it to work. I'm pretty sure the instructions you've been following
are designed for moving the installation to a different *local* drive,
not to a network drive, which is why this is not mentioned there.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[John R Pierce]

On 05/03/12 11:55 AM, EllyR wrote:

But after doing all the things mentioned in the link, when I want to start
the postgres again as a service, it can not be started, it seems it does not
recognize the map network drive , would you please let me know if I am in
the right track or I need to do something else? any help is appreciated.
Thanks.


services do not run on your desktop session, they run in their own 
service user context, and can't see mapped drives created by your 
desktop session.


furthermore, a database server really shouldn't be running on a network 
shared drive in the first place, they aren't reliable for random block 
write oriented IO, which databases do a LOT of.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

[GENERAL] Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

[EllyR]

Hi,

I have a huge Postgres database on my C drive, I am continuously collecting
the data, so unfortunately, the drive is getting full and I wanted to move
it to a map network drive I got. I followed the instruction in the link
below:

http://kb.vircom.com/kbase/default.asp?id=1512&Lang=1&SID=

1 - Stop the Postgres and Modus Monitor services
TIP: save a step & stop both services directly from Administrative Tools >
Services panel on your Windows server

2 - Copy C:\Program Files\PostgreSQL\data\*.* to the destination directory
(ex. D:\pgdata\)

3 - Open the Registry Editor

4 - Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\pgsql-8.0

- Modify ImagePath:

From: C:\Program Files\PostgreSQL\bin\pg_ctl.exe" runservice -N "pgsql-8.0"
-D "C:\Program Files\PostgreSQL\data\"

To: C:\Program Files\PostgreSQL\bin\pg_ctl.exe" runservice -N "pgsql-8.0" -D
"d:\pgdata\" (where d:\pgdata = your new PostgreSQL directory)


5 - Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installatio ns

- Look for and open the alpha-numeric subfolder

- Modify Data Directory:

From: C:\Program Files\PostgreSQL\data\

To: D:\pgdata\ (where d:\pgdata = your new PostgreSQL directory)


6 - Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\pg sql-8.0

- Modify Data Directory:

From: C:\Program Files\PostgreSQL\data\

To: D:\pgdata\ (where d:\pgdata = your new PostgreSQL directory)


IMPORTANT: By default, Modus installs PostgreSQL to run under a user called
Postgres. The data directory requires proper permissions in order to allow
the service to have access.

7 - Open Windows Explorer

- Go to the new PostgreSQL data directory in Windows Explorer
- Right-click and select Properties
- Click on the Security
- Click on Add to add the Postgres user
- Give the Postgres user the permission to Modify the directory


8 - Start the PostgreSQL and Modus Monitor services from the Administrative
Tools > Services panel on your server


But after doing all the things mentioned in the link, when I want to start
the postgres again as a service, it can not be started, it seems it does not
recognize the map network drive , would you please let me know if I am in
the right track or I need to do something else? any help is appreciated.
Thanks.

Elly.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] privs drop after alter table

[Josh Kupershmidt]

On Thu, May 3, 2012 at 11:14 AM, Gauthier, Dave  wrote:
> v8.3.4 on linux
> After this..
>
> alter table fred alter column wilma set default 'dino';
>
> ...it appeared that a user that had insert,delete,update privs lost them.
>
> I regranded and things went back to normal.
>
> Is it correct for those privs to be dropped in this case?  If so, why (just
> curious).

I don't think the behavior you described would be correct. But works
for me on 8.3.18 (see testcase below), and I don't see any bugfixes
like this in the 8.3.x release notes. Are you able to produce a
self-contained test case?

Josh

-- These commands were run as user 'josh'
test=# CREATE TABLE fred (wilma text);
CREATE TABLE
test=# GRANT INSERT, DELETE, UPDATE ON fred TO dummy;
GRANT
test=# \z fred
   Access privileges for database "test"
 Schema | Name | Type  | Access privileges
+--+---+---
 public | fred | table | {josh=arwdxt/josh,dummy=awd/josh}
(1 row)

test=# ALTER TABLE fred ALTER COLUMN wilma SET DEFAULT 'dino';
ALTER TABLE
test=# \z fred
   Access privileges for database "test"
 Schema | Name | Type  | Access privileges
+--+---+---
 public | fred | table | {josh=arwdxt/josh,dummy=awd/josh}
(1 row)

-- 
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] Issue with rsync based incremental backup : postgres: startup process waiting for 0000000100000001000000D2

[Michael Nolan]

On Thu, May 3, 2012 at 11:49 AM, Samba  wrote:

> Hi,
>
>
> Please advise me if what i'm doing is makes sense and is an accepted
> mechanism for taking backups or if there is any other procedure that i can
> emplpoy to avoid unnecessarily archiving gigabytes of WAL logs which may be
> growing many times the size of the actual data directory.
>
>
> Thanks and Regards,
> Samba
>

The problem is that rsync isn't copying all the xlog files created during
the time the rsync is taking place, which is why it is complaining that
there are files missing.

There may be other logical flaws with your process as well.

Something similar to the steps given in "Starting Replication with only a
Quick Master Restart" as laid out in the wiki tutorial on binary
replication might give you a way to make this work.  (You probably won't
need the restart of the master, since you're not actually setting up
replication, so you won't be changing the postgresql.conf file on your
master.)

This uses a two-step process.  First you copy all the files EXCEPT the ones
on pg_xlog, then you copy those files, so you have a complete set.

See http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
--
Mike Nolan

[GENERAL] privs drop after alter table

[Gauthier, Dave]

v8.3.4 on linux

After this..
alter table fred alter column wilma set default 'dino';

...it appeared that a user that had insert,delete,update privs lost them.
I regranded and things went back to normal.

Is it correct for those privs to be dropped in this case?  If so, why (just 
curious).

Thanks in Advance

Re: [GENERAL] Uppercase to lowercase trigger?

[Darren Duncan]

Chrishelring wrote:

Hmm.. Haden´t thought about making a view. It should work because the
application that uses the data is read-only (yet!).

I´ll give that a try. Thanks!


Well a view is a much more natural solution than the other proposal, about 
constantly doing data definition / changing the schemas for each access like 
this proposal.  In fact, what you talk about sounds like an ideal case for 
views; you could have 2 complete schemas, one per application, where each 
corresponds to the other but one is lowercase and one is uppercase, and one is a 
view. -- Darren Duncan


--
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] pqlib garbage collection

[Martijn van Oosterhout]

On Thu, May 03, 2012 at 08:33:06AM +0200, Alexander Reichstadt wrote:
> Hi,
> 
> since I got no answer so far I searched through the docu again. I
> searched for GC as well as Garbage, and all garbage refers to is with
> regard to vacuuming a database.  But my question refers to wether or
> not memory management is with garbage collection supported or not. 
> When I try to link against pqlib, it seems I need to have garbage
> collection enabled in order for it to link.  But the documentation
> mentions it nowhere.

(I'm assuming you're referring to libpq, the C library).

There is no magic garbage collection. You must use PQclear.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

[GENERAL] Issue with rsync based incremental backup : postgres: startup process waiting for 0000000100000001000000D2

[Samba]

Hi,


I'm trying to make a periodic (daily) incremental backup using rsync but
noticing a message that postgres startup process is waiting on so and so
XLOG file indefinitely.

I intend to schedule a cron job to periodically execute this rsync backup
process by : a) stop postgres server on backup server b) rsync the data
directory of original server to backup server c) start postgres on
secondary d) ensure that postgres starts up with out any issue e) execute
some test cases so as to ensure that there no data integrity issues f)
execute more tests to ensure that data is valid from business logic
perspective.

One may ask why am i using rsync backup instead of the log shipping bases
streaming replication; well, my answer would be that our system would be
having a lot of updates and fewer additions and since the transactions
would be piling up for each update, the WAL files accumulate so much that
we would be needing to archive much more data than the actual changes.
Further, transferring such huge amount of logs is going to consume lot of
time and bandwidth. Instead, I suppose that with rsync based backup we just
need to sync the actual data files and need to worry about archiving the
older WAL files, thus saving on the time of transfer, bandwidth costs, and
WAL archive management.

I would like to know if syncing the $PGDATA directory with rsync would be
good enough for incremental backups or that would create
data integrity issues.

Everytime i run the rsync command, I could successfully start the backup
server and am able to see the latest data from the original server, but am
a bit worried about this message which may be hinting that the backup is
not complete and is missing some essential WAL files.

Is there any additional steps or rsync configuration options that i need to
specify in order to make this sync complete?

Please let me know what can be done to make the rsync process complete so
that the backup server does not complain about waiting for missing WAL
files.

Here are the steps i executed on master and slave:

On Backup Server
root@backup ~]#
root@backup ~]# service postgresql stop
Stopping postgresql service:   [  OK  ]
root@backup ~]#

On Original Server
root@original ~]#
root@original ~]# su -l postgres -s /bin/bash -c "psql -d test"
test=#
test=# SELECT pg_start_backup('rsync_backup');
test=#\q
root@original ~]#
root@original ~]# rsync --archive --checksum --compress --progress
 --exclude postgresql.conf --exclude pg_hba.conf --exclude server.crt
--exclude server.key /var/lib/pgsql/data/* root@backup.server
:/var/lib/pgsql/data/
root@original ~]# su -l postgres -s /bin/bash -c "psql -d test"
test=#
test=# SELECT pg_stop_backup();
test=#\q
root@original~]#

On Backup Server
root@backup ~]#
root@backup ~]# service postgresql start
Starting postgresql service:   [  OK  ]
root@backup ~]#
root@backup  ~]#  ps aux  | grep postgres
postgres 18210  5.3  1.5 1811408 88540 ?   S20:43   0:00
/usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 18220  0.0  0.0 115496  1144 ?Ss   20:43   0:00 postgres:
logger process
*postgres 18221  0.0  0.0 1812252 2328 ?Ss   20:43   0:00 postgres:
startup process   waiting for 0001000100D2*
postgres 18222  0.0  0.0 1812180 2280 ?Ss   20:43   0:00 postgres:
writer process
postgres 18223  0.0  0.0 117744  1388 ?Ss   20:43   0:00 postgres:
stats collector process
postgres 18337  0.6  0.1 1814308 8092 ?Ss   20:43   0:00 postgres:
avaya_system_app_user avmgmt 127.0.0.1(34207) idle
postgres 18406  0.4  0.1 1815792 9272 ?Ss   20:43   0:00 postgres:
avaya_system_app_user avmgmt 127.0.0.1(34217) idle
postgres 18477  0.0  0.1 1813684 6680 ?Ss   20:43   0:00 postgres:
avaya_sseuser avmgmt 127.0.0.1(34231) idle
root 18479  0.0  0.0  61160   728 pts/1R+   20:43   0:00 grep
postgres
root@backup ~]#

Please advise me if what i'm doing is makes sense and is an accepted
mechanism for taking backups or if there is any other procedure that i can
emplpoy to avoid unnecessarily archiving gigabytes of WAL logs which may be
growing many times the size of the actual data directory.


Thanks and Regards,
Samba

Re: [GENERAL] Limit the normal user to see system catalog or not??? And create privilege???

[yxj]

Hi Super Guys,
 
Thanks. I learned  a lot. It's very good for me to know that.
 
Regards.
 
Grace





At 2012-05-03 07:15:29,"Bruce Momjian"  wrote:
>On Wed, May 02, 2012 at 04:03:01PM -0700, Adrian Klaver wrote:
>> On 05/02/2012 11:42 AM, Bruce Momjian wrote:
>> > On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote:
>> >> On 03/28/2012 09:54 AM, leaf_yxj wrote:
>> >>> For oracle, the normal user can't see all the system catalog. but for
>> >>> postgresql, it looks like all the user can see the system catalog.  
>> >>> Should
>> >>> we limit the user read privilege to system catalog?
>> >>>
>> >>> In oracle, the system privilege has create table, create view,create
>> >>> function.  For postgresql database, how to control the user who only can
>> >>> create table but can't create view. Based on the test I did, once the 
>> >>> user
>> >>> has the create privilege on the schema, the user will have any create
>> >>> privilege on that schema. In postgresql, Rule is used to control that ???
>> >>> very confused!
>> >>
>> >> Path to unconfusion:):
>> >> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html
>> >>
>> >> You can grant CREATE on a schema and then restrict CREATE within the
>> >> schema for different objects types. In recent versions you are
>> >> looking for ALL * IN SCHEMA schema_name where * is the object type.
>> > 
>> > I think the problem with ALL * IN SCHEMA it just applies permissions on
>> > all objects in the schema at a point in time, i.e. it doesn't apply to
>> > objects created _after_ that command was run.
>> 
>> True, but in the above was an explanation of default privileges which
>> led to this link:
>> 
>> http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html
>> 
>> ALTER DEFAULT PRIVILEGES does allow you to control what happens in the 
>> future.
>> Admittedly not the most obvious connection:)
>
>Oh, I forgot about that one.
>
>-- 
>  Bruce Momjian  http://momjian.us
>  EnterpriseDB http://enterprisedb.com
>
>  + It's impossible for everything to be true. +

Re: [GENERAL] SQL functions not being inlined

[Tom Lane]

Evan Martin  writes:
> Thanks, I went into that function, added log statements everywhere and 
> figured which check it's failing on:
> !heap_attisnull(func_tuple, Anum_pg_proc_proconfig)
> and it's because my real function had this at the end:

> SET search_path FROM CURRENT;

Well, shame on you for omitting that critical detail from your example.

> which I never imagined would make any difference.

The reason SET options disable inlining is that inlining would leave
noplace for the SET to be applied and then removed.  In some cases it's
possible that we could prove that the SET need not occur at runtime,
but the inlining mechanism doesn't have that much knowledge about
configuration parameters.

> This still doesn't 
> explain why it was being inlined sometimes - I didn't add and remove 
> that bit, it was there the whole time!

That does seem improbable.  You sure you were looking at just one
function?

> Is there any reason this stuff isn't documented? It can have huge 
> performance implications, so I'm surprised more people don't run into 
> it.

There are a huge number of details of planner behavior that "can have
huge performance implications", and they change frequently.  It's not
clear to me that we can document this stuff in a way that's better than
referring to the source code.

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] Re: How to insert random character data into tables for testing purpose. THanks.

[yxj]

Hi Bruce,
 
Thanks. I tried. It works.
Regards.
 
Grace





At 2012-05-03 07:14:21,"Bruce Momjian"  wrote:
>On Wed, May 02, 2012 at 11:03:47AM -0700, leaf_yxj wrote:
>> Hi Andreas,
>> 
>> Thanks a lot.
>> 
>> I tried those following , they work.
>> 
>> 1) insert into test values ( generate_series(1,1000),
>> md5(random()::text));
>> 
>> 
>> 2) create table t_random as select s, md5(random()::text) from
>> generate_Series(1,5) s;
>
>Here is a method that generates strings with a little more variability:
>
>   SELECT
>(
>SELECT string_agg(x, '')
>FROM (
>SELECT chr(ascii('A') + (random() * 25)::integer)
>   -- reference 'b' so it is correlated and re-evaluated
>FROM generate_series(1, 40 + b * 0)
>) AS y(x)
>)
>   FROM generate_series(1,5) as a(b);
>   
>   string_agg
>   --
>JWNRUIEPJQGDVBXDJDTROFSNMKGVNAQWWEORMSHV
>KNYGOWJXBEPOJICURIQGIIDFMRXYLFUUSTCIRLGR
>PVHXEITVQKBZREFEVDDHHYCASEIKPOCFJJDNRWJJ
>HKIYONDLQGSXFUXXZGHPUKUESXRLUATBLCAAPDLG
>JEENYCLYISODDIGWGXHBYCCJECJTFUAOYECXGNWO
>   (5 rows)
>
>-- 
>  Bruce Momjian  http://momjian.us
>  EnterpriseDB http://enterprisedb.com
>
>  + It's impossible for everything to be true. +

Re: [GENERAL] Removal of BSDi, BSD/OS port

[Bruce Momjian]

On Wed, May 02, 2012 at 12:31:02PM -0400, Bruce Momjian wrote:
> I assume there are no current users of the BSDi, BSD/OS port that are
> going to be upgrading to Postgres 9.2.  Therefore, I propose removal of
> that port in Postgres 9.2.  Any objections?

Port removed for Postgres 9.2.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] SQLSTATE XX000 Internal Error 7

[Tom Lane]

Ben Madin  writes:
> On 03/05/2012, at 11:57 AM, Tom Lane wrote:
>> So this is smelling like a kernel or filesystem bug.  I wonder exactly
>> which OS X update you're running, and what sort of filesystem the
>> database is stored on.

> [ it's an up-to-date, pretty vanilla looking OS X Lion installation ]

Hm, so no smoking gun visible there.  Can you put together a
self-contained test case that triggers this error for you?  I recognize
that it'll be of the form "if you run this long enough, it'll fail"
rather than a deterministic failure, but that's OK.  What we want to
know at this point is if anyone else can reproduce it.  If we can do
that, and can show positively that the kernel is returning EINVAL for
valid-looking open() arguments, we'll have the needed ammunition to
file a bug with Apple.

> (PS How did you come to deciding that it was EINVAL - is that 'Error INVALid 
> argument'?)

The "Invalid argument" part of our error message comes from strerror(),
and that's the standard text for EINVAL.

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

[GENERAL] Encryption - searching and sorting

[David Welton]

Hi,

We have a situation where HIPAA data that needs to be encrypted.
Since we have lots of users, and a number of users who access the data
of different people, we cannot simply encrypt the disk and call it
good - it's not fine-grained enough.

So far, we've been encrypting each row, and that actually works out
fairly well, but now that we need to do searching and sorting, things
have naturally become a bit more difficult...

I've been testing a few different solutions with our data, which
shouldn't exceed 10,000 rows or thereabouts, in terms of what needs to
be encrypted/decrypted/searched and sorted.

I prototyped something like this:
http://www.doc.ic.ac.uk/teaching/distinguished-projects/2009/w.harrower.pdf
in Ruby (we're using Rails), and the performance is pretty good (well,
insertion is pretty slow, but that's ok for us), and also allows us to
search for substrings.

However, that did nothing about sorting.  So the next idea was to do
something like this: save a list of names (the data we're storing that
must be encrypted) and database row id's as a Ruby list, and encrypt
that  encrypt(marshal([list ... of ... names])).  The advantage over
having to decrypt single rows is that it seems to be a lot faster to
decrypt one big chunk of data rather than lots of little things.
Searching through a list of N thousand names is actually fairly quick
in Ruby, as is sorting.  So... this would probably work, but it's
pretty gross as a solution in that we're going to have to manually
keep a lot of data synced, and it feels awfully strange to be doing
everything in the application.

However, I can't think of a way to create an "index" like that in
Postgres, either.  Am I overlooking something?  The trick, I think, is
to keep the encrypt/decrypt operations to a minimum even if that
requires encrypting/decrypting a lot of data at once.  Perhaps
something like decrypting to a temporary table, running the queries I
need, and then dumping and encrypting the table back to its binary
field?

Thoughts?

Thank you,
-- 
David N. Welton

http://www.dedasys.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] How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?

[Raghavendra]

On Thu, May 3, 2012 at 6:52 PM, Siva Palanisamy  wrote:

>  Hi there! I'm interested to get the physical locations of tables, views,
> functions, data/content available in the tables of PostgreSQL in Linux OS.
> I've a scenario that PostgreSQL could be installed in SD-Card facility and
> Hard-Disk. If I've tables, views, functions, data in SD, I want to get the
> physical locations of the same and merge/copy into my hard-disk whenever I
> wish to replace the storage space. I hope the storage of database should be
> in terms of plain files architecture.
>
You first step should start from $PGDATA/base/, you find OID's as
directories which are related to each database of your cluster. In OID's
directory, you find all the objects ID's for Tables/indexes/view etc.,

To know the object id, you can use a system defined function.

postgres=# select pg_relation_filepath('foo');
 pg_relation_filepath
--
 base/12780/16407
(1 row)

12780, is database OID.

For moving objects from one drive to other, you need to use tablespaces.

http://www.postgresql.org/docs/9.1/static/sql-createtablespace.html

> 
>
> Also, can I able to see the contents by opening its files? I mean, can I
> able to access it? Please help me on this. Thanks!
>
> **
>
You cannot know the contents in files, unless u are good hacker :)

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[GENERAL] How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?

[Siva Palanisamy]

Hi there! I'm interested to get the physical locations of tables, views, 
functions, data/content available in the tables of PostgreSQL in Linux OS. I've 
a scenario that PostgreSQL could be installed in SD-Card facility and 
Hard-Disk. If I've tables, views, functions, data in SD, I want to get the 
physical locations of the same and merge/copy into my hard-disk whenever I wish 
to replace the storage space. I hope the storage of database should be in terms 
of plain files architecture.

Also, can I able to see the contents by opening its files? I mean, can I able 
to access it? Please help me on this. Thanks!





::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

---

Re: [GENERAL] SQL functions not being inlined

[Evan Martin]

Thanks, I went into that function, added log statements everywhere and 
figured which check it's failing on:


!heap_attisnull(func_tuple, Anum_pg_proc_proconfig)

and it's because my real function had this at the end:

SET search_path FROM CURRENT;

which I never imagined would make any difference. This still doesn't 
explain why it was being inlined sometimes - I didn't add and remove 
that bit, it was there the whole time! But at least the fix is simple - 
remove it.


Is there any reason this stuff isn't documented? It can have huge 
performance implications, so I'm surprised more people don't run into 
it. Even better would be some query that checks whether a function is 
inlineable - maybe not perfectly, but it could detect a few of the 
reasons just from pg_proc, right?


Regards,

Evan

On 2/05/2012 11:41 PM, Tom Lane wrote:

Evan Martin  writes:

This worked... at first. I did some simple queries and they showed the
function being inlined (index scan on primary key, seq scan - no
function scan). Very happy with that, I tried changing some other
functions (that depend on these) and then found that the _asof functions
are not being inlined anymore! I swear, I'm not making this up. Nothing
changed in those functions. Same simple query. It was inlined before and
now it's not. I've dropped and re-created the functions, did an ANALYZE,
even restarted PostgreSQL - they're not inlined any more. I really don't
know what to think!

[ squint... ]  There are a lot of undocumented restrictions on inlining
in inline_set_returning_function(), but AFAICS none of them are
nondeterministic, nor would the decision depend on anything outside
the function and its arguments.  Can you provide a concrete test case?

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] Uppercase to lowercase trigger?

[Chrishelring]

Hmm.. Haden´t thought about making a view. It should work because the
application that uses the data is read-only (yet!).

I´ll give that a try. Thanks!

Christian

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682880.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Memory Management in pqlib, Garbage Collection support

[Alexander Reichstadt]

(Sorry, I meant libpq). Actually it's finalize in Objective-C as well. PGSQLKit 
is the ObjC wrapper framework for libpq. I was confused by what I had learnt 
about GC, being it can't mix with non-GC. What the docu didn't mention in the 
places I read --or at least not so that it stuck-- was that it does link 
against C-libs regardless of GC and without throwing any kind of warning or 
error. 

So, if I understand this correctly, if I want to use GC in my frontend app, 
which links against PGSQLKit which links against libpq, I need to enhance 
PGSQLKit to support GC by adding finalizers for management of libpq's memory 
use to make GC available in all projects wanting to link against PGSQLKit, yes?



Am 03.05.2012 um 10:32 schrieb Simon Riggs:
Am 03.05.2012 um 10:13 schrieb Peter Bex:
> 
> OK, so you're specifically talking about Objective C.  I don't know much
> about that language, but unless there are specific ObjC-bindings for
> libpq (like libpq++ for C++), you'll need to perform manual memory
> management and call PQClear yourself.
> 
>> As GC is an evolutionary stage across languages [...]
> 
> […]

> I think if you want something similar you'll either need to write your
> own ObjC class to wrap the C functions or look for something pre-existing.
> Perhaps you can use libpq++.  I've also found a "PostgreSQL Cocoa Framework"
> project (but it appears to be abandoned): http://pgsqlcocoa.sourceforge.net/
Thanks for the pointer, but it's distributed under GPL, I can't use that. 
PGSQLKit is like PG, a BSD-style license. That's what brought me from MySQL to 
PG.

> 
>> In general there are libs that provide garbage collection for C as well, 
>> like here:
>> 
> 
> The Boehm GC is a good prototyping solution but not very suitable for
> general use.  It's an especially bad idea to force use of such a garbage
> collector on the user whenever using libpq.  This would also prohibit
> the integration of libpq with other languages and their GCs.

I didn't look inside, I really just looked up Wikipedia on the subject and if 
there are ways to get GC into C somehow and seemed to have gotten lucky.

> AFAIK libpq currently does not offer specifying callback functions to
> use for allocating and freeing objects (it uses malloc/free directly).
> If this was available, it would be even easier to integrate deeply with
> a custom GC.
> 
>> For example, it'd help avoid leaks like those caused by a result not being 
>> PQclear'ed.
> 
> C programmers are generally comfortable with manual memory management, or
> they'd use another language.

It's ok, I like Cocoa's GC though.


-- 
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] Memory Management in pqlib, Garbage Collection support

[Alban Hertroys]

On 3 May 2012 09:39, Alexander Reichstadt  wrote:
> Thanks, that's answering my question. In Objective-C as well as many other

I notice that you're talking about pqlib instead of libpq. Perhaps
pqlib is an Obj-C wrapper around libpq that most of us just don't know
about? Obj-C is not a particularly common programming language, after
all ;)

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
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] Memory Management in pqlib, Garbage Collection support

[Simon Riggs]

On Thu, May 3, 2012 at 8:56 AM, Magnus Hagander  wrote:
> On Thu, May 3, 2012 at 9:39 AM, Alexander Reichstadt  wrote:
>> Thanks, that's answering my question. In Objective-C as well as many other
>> languages there is the feature to turn on Garbage Collection. It's a
>> separate thread that scans memory for strong pointers, their source and
>> origin and "vacuums" memory so to not have any leaks. Anything unreferenced
>> and no longer needed is cleaned up automatically. There are some border
>> cases where GC can fail, but for most it works.
>
> libpq is a C library, not an objective-C library. So it clearly
> doesn't use an objective-c construct. If you are using some wrapper on
> top of libpq it might change things, but libpq itself has no knowledge
> of GC at all.

It would be nice to be able to garbage collect libpq programs
automatically though.

I think every time I read some libpq code I see an error.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
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] Memory Management in pqlib, Garbage Collection support

[Peter Bex]

On Thu, May 03, 2012 at 09:39:29AM +0200, Alexander Reichstadt wrote:
> Thanks, that's answering my question. In Objective-C as well as many other 
> languages there is the feature to turn on Garbage Collection. It's a separate 
> thread that scans memory for strong pointers, their source and origin and 
> "vacuums" memory so to not have any leaks. Anything unreferenced and no 
> longer needed is cleaned up automatically. There are some border cases where 
> GC can fail, but for most it works.

OK, so you're specifically talking about Objective C.  I don't know much
about that language, but unless there are specific ObjC-bindings for
libpq (like libpq++ for C++), you'll need to perform manual memory
management and call PQClear yourself.

> As GC is an evolutionary stage across languages [...]

Thank you for your explanation of GC but that really wasn't neccessary.
The confusion was mostly due to your mail omitting the fact that you
were using ObjC rather than C, which would be the default assumption
when talking about libpq.

Actually, my language of choice (Chicken Scheme) is also GCed and the
postgresql bindings I wrote for it integrate with its GC in such a way
as to automatically call PQClear when an object gets collected.  This
is easy to do in any language that supports "finalizers", and with a
little more effort it could even be integrated more tightly with the GC.
The user can also manually decide to clear up the memory used by a
result set when it's known in advance that this will no longer be needed
and the memory use is prohibitive.

I think if you want something similar you'll either need to write your
own ObjC class to wrap the C functions or look for something pre-existing.
Perhaps you can use libpq++.  I've also found a "PostgreSQL Cocoa Framework"
project (but it appears to be abandoned): http://pgsqlcocoa.sourceforge.net/

> In general there are libs that provide garbage collection for C as well, like 
> here:
> 

The Boehm GC is a good prototyping solution but not very suitable for
general use.  It's an especially bad idea to force use of such a garbage
collector on the user whenever using libpq.  This would also prohibit
the integration of libpq with other languages and their GCs.

AFAIK libpq currently does not offer specifying callback functions to
use for allocating and freeing objects (it uses malloc/free directly).
If this was available, it would be even easier to integrate deeply with
a custom GC.

> For example, it'd help avoid leaks like those caused by a result not being 
> PQclear'ed.

C programmers are generally comfortable with manual memory management, or
they'd use another language.

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

-- 
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] Memory Management in pqlib, Garbage Collection support

[Magnus Hagander]

On Thu, May 3, 2012 at 9:39 AM, Alexander Reichstadt  wrote:
> Thanks, that's answering my question. In Objective-C as well as many other
> languages there is the feature to turn on Garbage Collection. It's a
> separate thread that scans memory for strong pointers, their source and
> origin and "vacuums" memory so to not have any leaks. Anything unreferenced
> and no longer needed is cleaned up automatically. There are some border
> cases where GC can fail, but for most it works.

libpq is a C library, not an objective-C library. So it clearly
doesn't use an objective-c construct. If you are using some wrapper on
top of libpq it might change things, but libpq itself has no knowledge
of GC at all.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Uppercase to lowercase trigger?

[John R Pierce]

On 05/03/12 12:50 AM, Ken Tanzer wrote:
What if you created separate tables with the fields in uppercase, 
either with different names or better yet the same names and a 
different schema?  It seems you could put triggers on those tables, 
and have your trigger translate the field names and make the updates 
to the real tables instead.


if one of the applications is read only on this table,  you could use a 
view with the alternate version of the names.






--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] Uppercase to lowercase trigger?

[Ken Tanzer]

What if you created separate tables with the fields in uppercase, either
with different names or better yet the same names and a different schema?
 It seems you could put triggers on those tables, and have your trigger
translate the field names and make the updates to the real tables instead.

Seems like a lot of work, though, that could be avoided if you could get
your app to drop the quoting of field names, and have it automatically fold
to lower case...

On Thu, May 3, 2012 at 12:41 AM, Pavel Stehule wrote:

> Hello
>
> 2012/5/3 Chrishelring :
> > Maybe I should clarify what I mean.
> >
> > I have a db (postgresql 8.4.1 with a postGIS spatial extension) with
> about
> > 200+ tables in it. Some of them is supposed to be updated using an
> > application what requres that some of the columns are in uppercase (a
> > leftover from Oracle I suppose) but the main application that uses these
> > tables requres that the columns are in lowercase. I had an idea that
> using a
> > trigger I could chance the columns from lowercase to uppercase before
> > updating /changing the tables and after the update chance them back to
> > lowercase. Is that possible and how do I do that?
>
> no, this is not possible. But you can write simple functions that
> verifies names and you can call this function manually when you do
> some changes, or you can call this function from cron.
>
> Regards
>
> Pavel Stehule
>
>
> >
> > Christian
> >
> > --
> > View this message in context:
> http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682473.html
> > Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
AGENCY Software
A data system that puts you in control
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801

Re: [GENERAL] Uppercase to lowercase trigger?

[Pavel Stehule]

Hello

2012/5/3 Chrishelring :
> Maybe I should clarify what I mean.
>
> I have a db (postgresql 8.4.1 with a postGIS spatial extension) with about
> 200+ tables in it. Some of them is supposed to be updated using an
> application what requres that some of the columns are in uppercase (a
> leftover from Oracle I suppose) but the main application that uses these
> tables requres that the columns are in lowercase. I had an idea that using a
> trigger I could chance the columns from lowercase to uppercase before
> updating /changing the tables and after the update chance them back to
> lowercase. Is that possible and how do I do that?

no, this is not possible. But you can write simple functions that
verifies names and you can call this function manually when you do
some changes, or you can call this function from cron.

Regards

Pavel Stehule


>
> Christian
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682473.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

Re: [GENERAL] Memory Management in pqlib, Garbage Collection support

[Alexander Reichstadt]

Thanks, that's answering my question. In Objective-C as well as many other 
languages there is the feature to turn on Garbage Collection. It's a separate 
thread that scans memory for strong pointers, their source and origin and 
"vacuums" memory so to not have any leaks. Anything unreferenced and no longer 
needed is cleaned up automatically. There are some border cases where GC can 
fail, but for most it works.

There are three values for the compiler: off, supported and required. One 
cannot mix subprojects unlike in e.g. ARC (Automated Reference Counting). 
Either it's on or off. Supported means that it covers both sides, "manual" 
memory management and automated management.

As GC is an evolutionary stage across languages, objects formerly deallocated 
or freed or released (or whatever other term is used across different 
languages) through a unique call or method name where one would normally also 
host any steps to release associated resources not necessarily memory related 
(closing sockets, file handles etc.), such call is replaced with the language 
specific call that the garbage collector calls to still provide for closing 
these resources while still being able to handle memory management in an 
automated fashion. All directly memory related calls become no-ops to provide 
for backward compatibility. In the end one can compile with GC on, or GC off 
and the compiler refers to the calls it needs to deal with the two aspects 
accordingly, either resource-only management or resource-and-memory management.

In general there are libs that provide garbage collection for C as well, like 
here:


For example, it'd help avoid leaks like those caused by a result not being 
PQclear'ed.

Alex


Am 03.05.2012 um 09:16 schrieb Peter Bex:

> On Thu, May 03, 2012 at 09:08:53AM +0200, Alexander Reichstadt wrote:
>> 
>> Hi,
>> 
>> since I got no answer so far I searched through the docu again. I searched 
>> for GC as well as Garbage, and all garbage refers to is with regard to 
>> vacuuming a database. But my question refers to wether or not memory 
>> management is with garbage collection supported or not. When I try to link 
>> against pqlib, it seems I need to have garbage collection enabled in order 
>> for it to link. But the documentation mentions it nowhere.
> 
> What kind of garbage collection do you need to have enabled in order to link?
> 
> C is based on manual memory-management, and you can't generally have
> garbage-collection in it.  Hence, libpq is *not* garbage-collected and
> your statement that it needs "garbage collection enabled" doesn't make
> much sense to me.
> 
> Are you talking about libpq bindings to some other language, perhaps?
> 
>> Please, can someone confirm this or is this the wrong list?
> 
> This is the right list.
> 
> Cheers,
> Peter
> -- 
> http://sjamaan.ath.cx
> --
> "The process of preparing programs for a digital computer
> is especially attractive, not only because it can be economically
> and scientifically rewarding, but also because it can be an aesthetic
> experience much like composing poetry or music."
>   -- Donald Knuth
> 
> -- 
> 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] pqlib garbage collection

[Alexander Reichstadt]

Hi,

since I got no answer so far I searched through the docu again. I searched for 
GC as well as Garbage, and all garbage refers to is with regard to vacuuming a 
database. But my question refers to wether or not memory management is with 
garbage collection supported or not. When I try to link against pqlib, it seems 
I need to have garbage collection enabled in order for it to link. But the 
documentation mentions it nowhere.

Please, can someone confirm this or is this the wrong list?

Thanks
Alex

Am 02.05.2012 um 23:32 schrieb Alexander Reichstadt:

> Hi,
> 
> regarding pqlib, from what I gather it is with garbage collection supported, 
> yes?
> 
> 
> If so, is PQclear a left-over or still mandatory for avoiding leaks?
> 
> 
> Thanks
> Alex
> 
> 



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [GENERAL] Memory Management in pqlib, Garbage Collection support

[John R Pierce]

On 05/03/12 12:08 AM, Alexander Reichstadt wrote:

since I got no answer so far I searched through the docu again. I searched for 
GC as well as Garbage, and all garbage refers to is with regard to vacuuming a 
database. But my question refers to wether or not memory management is with 
garbage collection supported or not. When I try to link against pqlib, it seems 
I need to have garbage collection enabled in order for it to link. But the 
documentation mentions it nowhere.


what is this garbage collection you are talking about having to 
enable?I'm not aware of any such options in C, and this is a C API.  
not Java.  not Python.  not C++.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] Uppercase to lowercase trigger?

[Chrishelring]

Maybe I should clarify what I mean. 

I have a db (postgresql 8.4.1 with a postGIS spatial extension) with about
200+ tables in it. Some of them is supposed to be updated using an
application what requres that some of the columns are in uppercase (a
leftover from Oracle I suppose) but the main application that uses these
tables requres that the columns are in lowercase. I had an idea that using a
trigger I could chance the columns from lowercase to uppercase before
updating /changing the tables and after the update chance them back to
lowercase. Is that possible and how do I do that?

Christian

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682473.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Memory Management in pqlib, Garbage Collection support

[Peter Bex]

On Thu, May 03, 2012 at 09:08:53AM +0200, Alexander Reichstadt wrote:
> 
> Hi,
> 
> since I got no answer so far I searched through the docu again. I searched 
> for GC as well as Garbage, and all garbage refers to is with regard to 
> vacuuming a database. But my question refers to wether or not memory 
> management is with garbage collection supported or not. When I try to link 
> against pqlib, it seems I need to have garbage collection enabled in order 
> for it to link. But the documentation mentions it nowhere.

What kind of garbage collection do you need to have enabled in order to link?

C is based on manual memory-management, and you can't generally have
garbage-collection in it.  Hence, libpq is *not* garbage-collected and
your statement that it needs "garbage collection enabled" doesn't make
much sense to me.

Are you talking about libpq bindings to some other language, perhaps?

> Please, can someone confirm this or is this the wrong list?

This is the right list.

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

-- 
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] SQLSTATE XX000 Internal Error 7

[Martijn van Oosterhout]

On Thu, May 03, 2012 at 02:46:16PM +0800, Ben Madin wrote:
> (PS How did you come to deciding that it was EINVAL - is that 'Error INVALid 
> argument'?)

It's one of the standard error codes, see for example
http://www.jbox.dk/sanos/source/include/errno.h.html

Tom is right, it's not clear how this error can occur. Linux does it if
you ask for O_DIRECT on a filesystem that doesn't support it, but it
doesn't look like that's the problem here either.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

[GENERAL] Memory Management in pqlib, Garbage Collection support

[Alexander Reichstadt]

Hi,

since I got no answer so far I searched through the docu again. I searched for 
GC as well as Garbage, and all garbage refers to is with regard to vacuuming a 
database. But my question refers to wether or not memory management is with 
garbage collection supported or not. When I try to link against pqlib, it seems 
I need to have garbage collection enabled in order for it to link. But the 
documentation mentions it nowhere.

Please, can someone confirm this or is this the wrong list?

Thanks
Alex

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