[GENERAL] I don't understand something...
Hello.
I was asked a simple question. We have table employees:
\d employees
Table public.employees
Column |Type |
Modifiers
+-+-
employee_id| integer | not null default
nextval('employees_employee_id_seq'::regclass)
first_name | character varying(20) |
last_name | character varying(25) | not null
email | character varying(25) | not null
phone_number | character varying(20) |
hire_date | timestamp without time zone | not null
job_id | character varying(10) | not null
salary | numeric(8,2)|
commission_pct | numeric(2,2)|
manager_id | integer |
department_id | integer |
Indexes:
employees_pkey PRIMARY KEY, btree (employee_id)
emp_email_uk UNIQUE, btree (email)
emp_department_ix btree (department_id)
emp_job_ix btree (job_id)
emp_manager_ix btree (manager_id)
emp_name_ix btree (last_name, first_name)
Check constraints:
emp_salary_min CHECK (salary 0::numeric)
Foreign-key constraints:
employees_department_id_fkey FOREIGN KEY (department_id)
REFERENCES departments(department_id)
employees_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(job_id)
employees_manager_id_fkey FOREIGN KEY (manager_id) REFERENCES
employees(employee_id)
Referenced by:
TABLE departments CONSTRAINT dept_mgr_fk FOREIGN KEY
(manager_id) REFERENCES employees(employee_id)
TABLE employees CONSTRAINT employees_manager_id_fkey FOREIGN
KEY (manager_id) REFERENCES employees(employee_id)
TABLE job_history CONSTRAINT job_history_employee_id_fkey
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
Now we want to select count of all employees who doesn't have any
subordinates (query 1):
SELECT count(employee_id) from employees o where not exists (select 1
from employees where manager_id=o.employee_id);
count
---
89
(1 row)
We can select count of all managers (query 2):
SELECT count(employee_id) from employees where employee_id in (select
manager_id from employees);
count
---
18
(1 row)
But if we reformulate the first query in the same way, answer is
different (query 3):
SELECT count(employee_id) from employees where employee_id not in
(select manager_id from employees) (query 3);
count
---
0
(1 row)
I don't understand why queries 1 and 3 give different results. They
seems to be the same... Could someone explain the difference?
--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University
--
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] I don't understand something...
Alexander, that's a classic one,
rewrite your last query as :
SELECT count(employee_id) from employees where employee_id not in
(select manager_id from employees WHERE manager_id IS NOT NULL);
NULLS semantics are sometimes not so obvious.
Στις Monday 03 October 2011 09:33:12 ο/η Alexander Pyhalov έγραψε:
Hello.
I was asked a simple question. We have table employees:
\d employees
Table public.employees
Column |Type |
Modifiers
+-+-
employee_id| integer | not null default
nextval('employees_employee_id_seq'::regclass)
first_name | character varying(20) |
last_name | character varying(25) | not null
email | character varying(25) | not null
phone_number | character varying(20) |
hire_date | timestamp without time zone | not null
job_id | character varying(10) | not null
salary | numeric(8,2)|
commission_pct | numeric(2,2)|
manager_id | integer |
department_id | integer |
Indexes:
employees_pkey PRIMARY KEY, btree (employee_id)
emp_email_uk UNIQUE, btree (email)
emp_department_ix btree (department_id)
emp_job_ix btree (job_id)
emp_manager_ix btree (manager_id)
emp_name_ix btree (last_name, first_name)
Check constraints:
emp_salary_min CHECK (salary 0::numeric)
Foreign-key constraints:
employees_department_id_fkey FOREIGN KEY (department_id)
REFERENCES departments(department_id)
employees_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(job_id)
employees_manager_id_fkey FOREIGN KEY (manager_id) REFERENCES
employees(employee_id)
Referenced by:
TABLE departments CONSTRAINT dept_mgr_fk FOREIGN KEY
(manager_id) REFERENCES employees(employee_id)
TABLE employees CONSTRAINT employees_manager_id_fkey FOREIGN
KEY (manager_id) REFERENCES employees(employee_id)
TABLE job_history CONSTRAINT job_history_employee_id_fkey
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
Now we want to select count of all employees who doesn't have any
subordinates (query 1):
SELECT count(employee_id) from employees o where not exists (select 1
from employees where manager_id=o.employee_id);
count
---
89
(1 row)
We can select count of all managers (query 2):
SELECT count(employee_id) from employees where employee_id in (select
manager_id from employees);
count
---
18
(1 row)
But if we reformulate the first query in the same way, answer is
different (query 3):
SELECT count(employee_id) from employees where employee_id not in
(select manager_id from employees) (query 3);
count
---
0
(1 row)
I don't understand why queries 1 and 3 give different results. They
seems to be the same... Could someone explain the difference?
--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University
--
Achilleas Mantzios
--
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] I don't understand something...
On 10/03/2011 11:19, Achilleas Mantzios wrote: Alexander, that's a classic one, rewrite your last query as : SELECT count(employee_id) from employees where employee_id not in (select manager_id from employees WHERE manager_id IS NOT NULL); NULLS semantics are sometimes not so obvious. Thanks. It's confusing, that IN check works as expected, but NOT IN works this way... -- Best regards, Alexander Pyhalov, system administrator of Computer Center of Southern Federal University -- 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] One-click installer, Windows 7 32-bit, and icacls.exe
On Fri, Sep 30, 2011 at 15:34, Karl Wright daddy...@gmail.com wrote: I saw a thread where somebody saw icacls.exe being called by the one-click installer. I'm having the same thing - the installer has been running for 45 minutes now and is basically going to have to be stopped because I'm out of time waiting for it. Looking at process monitor, it is clear that icacls.exe is going through every file on the entire system and changing its permissions. The process tree indicates that it is a child of the installer, and that it is running the command: icacls C:\ /grant kawright:RX Clearly this won't do at all and should be considered a severe installer bug. If it does, it certainly sounds like a very bad bug. However, according to the documentation for icacls (http://technet.microsoft.com/en-us/library/cc753525(WS.10).aspx), you should use /t to get it to traverse into subdirectories, and clearly it's not doing that. So I wonder why it would go across the whole filesystem - might tbere be a bug in icacls? Or maybe it has something to do with inheritance? The way inheritance-permissions works on ntfs is, um, let's call it interesting. Maybe it needs to specify the (NP) flag to not propagate inheritance or something? -- 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] I don't understand something...
On Mon, Oct 03, 2011 at 11:48:45AM +0400, Alexander Pyhalov wrote: On 10/03/2011 11:19, Achilleas Mantzios wrote: Alexander, that's a classic one, rewrite your last query as : SELECT count(employee_id) from employees where employee_id not in (select manager_id from employees WHERE manager_id IS NOT NULL); NULLS semantics are sometimes not so obvious. Thanks. It's confusing, that IN check works as expected, but NOT IN works this way... If I might plug: http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/ Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
On Mon, Oct 3, 2011 at 8:59 AM, Magnus Hagander mag...@hagander.net wrote: On Fri, Sep 30, 2011 at 15:34, Karl Wright daddy...@gmail.com wrote: I saw a thread where somebody saw icacls.exe being called by the one-click installer. I'm having the same thing - the installer has been running for 45 minutes now and is basically going to have to be stopped because I'm out of time waiting for it. Looking at process monitor, it is clear that icacls.exe is going through every file on the entire system and changing its permissions. The process tree indicates that it is a child of the installer, and that it is running the command: icacls C:\ /grant kawright:RX Clearly this won't do at all and should be considered a severe installer bug. If it does, it certainly sounds like a very bad bug. However, according to the documentation for icacls (http://technet.microsoft.com/en-us/library/cc753525(WS.10).aspx), you should use /t to get it to traverse into subdirectories, and clearly it's not doing that. So I wonder why it would go across the whole filesystem - might tbere be a bug in icacls? Yes - that's how it's supposed to work (ie. *not* using /t). The purpose of that code is to ensure that the entire path leading up to the data/installation directories is readable by the users that need it. We've had a number of reported installation failures in the past caused by weirdness where read or execute permissions weren't available for (for example) the service account user, which caused somewhat mysterious failures. Or maybe it has something to do with inheritance? The way inheritance-permissions works on ntfs is, um, let's call it interesting. Maybe it needs to specify the (NP) flag to not propagate inheritance or something? Sachin/Ashesh; can one of you investigate this please? Karl; can you please provide precise details of your Windows version, and anything unusual about your disk configuration? I know this doesn't happen on any of the installations of Windows 7 that we use for testing (which tend to be the MSDN builds, running on local NTFS disks), so I wonder if there's an icacls bug in a specific build or rev of Windows, or when used on a certain type of filesystem. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PL/Python
Maybe I'm misunderstanding something, but isn't such a sql file an extension or is 95% of the way there? Pyrseas is already distributed via PGXN, but like some other PGXN extensions (dbi-link?), it doesn't actually create functions in the database. Its two utilities run entirely as external programs. If the Pyrseas functions were added via an .sql file to a database, EXTENSION or not, they would be available for use by non-Pyrseas programs, e.g., pgAdmin could call diff_map() to compare database objects, Perl scripts or even a plain psql script could call to_yaml(). And these would not depend on psycopg2, which currently *is* a Pyrseas dependency (it would still be necessary for access to the command line utilities). Joe I don't know if there is an official definition, but an extension is generally a compiled program that is accessed by the SQL. It must be compiled with the postgresql headers and have the magic number defined. The compiled file must then be put into the lib directory with the other postgresql extensions. A user defined function does extend postgresql, but it doesn't have any of the complications. -- 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] One-click installer, Windows 7 32-bit, and icacls.exe
Dave Page wrote on 03.10.2011 10:11: Karl; can you please provide precise details of your Windows version, and anything unusual about your disk configuration? I know this doesn't happen on any of the installations of Windows 7 that we use for testing (which tend to be the MSDN builds, running on local NTFS disks), so I wonder if there's an icacls bug in a specific build or rev of Windows, or when used on a certain type of filesystem. I have reported this as well some weeks ago. For me this happened on a Windows7 64 bit system that is part of a domain (all current MS patches applied) UAC is turned off, I was logged in as a local administrator (*not* a domain administrator). I have a ProcessMonitor trace that shows the behaviour of what icacls is doing when started by the installer. It is available here: http://www.sql-workbench.net/icacls_trace.zip Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Savepoint or begin
I am having the following problem: I have upgrade scripts which are runnable one-by-one. I will also want to run all of them together. Example: table1.sql: begin; alter table table1 add column new_col1; alter table table1 add column new_col2; commit; table2.sql: begin; alter table table2 add column new_col1; alter table table2 add column new_col2; commit; upgrade_all.sql: begin; \i table1.sql \i table2.sql commit; If I run upgrade_all.sql, it will not be atomic, as table1.sql's COMMIT will commit half of the work and table2.sql's COMMIT will commit another half of the work. If there is an error when running table2.sql, this would commit half of the work and rollback half of the work. What I would like to do is something like: table1.sql: savepoint or begin s1; ... commit s1; If run outside transaction, this would be equivalent to table1.sql, that is SAVEPOINT OR BEGIN would create a new transaction, and COMMIT s1 would commit it. If run inside a transaction, this would create a savepoint and commit would not do anything. The syntax could of course be much better, but I hope this is enough to show what I am after. Is this doable already somehow? Am I doing my upgrade script structuring wrong? - Anssi Kääriäinen -- 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] Savepoint or begin
You might scrap all BEGIN/COMMIT/ROLLBACK stmts, and run your upgrade_all.sql as psql --single-transaction -f upgrade_all.sql Στις Monday 03 October 2011 12:36:58 ο/η Anssi Kääriäinen έγραψε: I am having the following problem: I have upgrade scripts which are runnable one-by-one. I will also want to run all of them together. Example: table1.sql: begin; alter table table1 add column new_col1; alter table table1 add column new_col2; commit; table2.sql: begin; alter table table2 add column new_col1; alter table table2 add column new_col2; commit; upgrade_all.sql: begin; \i table1.sql \i table2.sql commit; If I run upgrade_all.sql, it will not be atomic, as table1.sql's COMMIT will commit half of the work and table2.sql's COMMIT will commit another half of the work. If there is an error when running table2.sql, this would commit half of the work and rollback half of the work. What I would like to do is something like: table1.sql: savepoint or begin s1; ... commit s1; If run outside transaction, this would be equivalent to table1.sql, that is SAVEPOINT OR BEGIN would create a new transaction, and COMMIT s1 would commit it. If run inside a transaction, this would create a savepoint and commit would not do anything. The syntax could of course be much better, but I hope this is enough to show what I am after. Is this doable already somehow? Am I doing my upgrade script structuring wrong? - Anssi Kääriäinen -- Achilleas Mantzios -- 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] PL/Python
On 10/03/2011 04:14 AM, Sim Zacks wrote: I don't know if there is an official definition, but an extension is generally a compiled program that is accessed by the SQL. It must be compiled with the postgresql headers and have the magic number defined. The compiled file must then be put into the lib directory with the other postgresql extensions. A user defined function does extend postgresql, but it doesn't have any of the complications. As of 9.1 (and I *was* thinking in terms of 9.1 when I wrote EXTENSION), there is a definition in: http://www.postgresql.org/docs/9.1/static/sql-createextension.html http://www.postgresql.org/docs/9.1/static/extend-extensions.html You'll notice that in the latter there is an example of a SQL-only extension. That's why I thinking of Pyrseas as some plpython functions as a true EXTENSION. Joe -- 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] I don't understand something...
On 3 October 2011 08:33, Alexander Pyhalov a...@rsu.ru wrote: Now we want to select count of all employees who doesn't have any subordinates (query 1): SELECT count(employee_id) from employees o where not exists (select 1 from employees where manager_id=o.employee_id); count --- 89 (1 row) We can select count of all managers (query 2): SELECT count(employee_id) from employees where employee_id in (select manager_id from employees); count --- 18 (1 row) But if we reformulate the first query in the same way, answer is different (query 3): SELECT count(employee_id) from employees where employee_id not in (select manager_id from employees) (query 3); count --- 0 (1 row) I don't understand why queries 1 and 3 give different results. They seems to be the same... Could someone explain the difference? That's because NOT IN returns NULL if there are any NULLs in the list. As the WHERE-clause requires something to evaluate to either true or false (NULL won't do), you (correctly) get false if someone is a manager, but also if _anyone_ is NOT a manager. That's an artefact of how 3-valued logic is implemented in the SQL standard. -- 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] : PostgreSQL Online Backup
Another problem in recovery (probably because of rsync) - As said earlier, we are taking a production backup everyday incrementally using rsync. But, Postgres some how misses to sync few files in between and keeps on asking the back dated archive files (more than 1 week ago). I restored October 2nd backup and PG is asking for September 26th archive file with the last known time as 26th Sep, 2011. 2011-10-03 07:17:12 CDT [12705]: [1-1] LOG: database system was interrupted; last known up at 2011-09-26 09:01:36 CDT 2011-10-03 07:17:12 CDT [12705]: [2-1] LOG: starting archive recovery cp: cannot stat `/usr/local/pgsql9.0.1/obtdata/data/pg_xlog/000105390076': No such file or directory 2011-10-03 07:17:12 CDT [12705]: [3-1] LOG: could not open file pg_xlog/000105390076 (log file 1337, segment 118): No such file or directory 2011-10-03 07:17:12 CDT [12705]: [4-1] LOG: invalid checkpoint record 2011-10-03 07:17:12 CDT [12705]: [5-1] PANIC: could not locate required checkpoint record 2011-10-03 07:17:12 CDT [12705]: [6-1] HINT: If you are not restoring from a backup, try removing the file /usr/local/pgsql9.0.1/obtdata/data/backup_label. 2011-10-03 07:17:12 CDT [12702]: [1-1] LOG: startup process (PID 12705) was terminated by signal 6: Aborted 2011-10-03 07:17:12 CDT [12702]: [2-1] LOG: aborting startup due to startup process failure I always see pg_clog files and some base files not getting synced. Below is what we are doing - pg_start_backup() rsync the data directory pg_stop_backup() The first time rsync is fine, but, the subsequent runs are generating in-consistency. We do the same every day to backup the data directory incrementally. What i observed is PG records the TXN id when ever backup starts and stops + backup label. The next day when PG records the start backup time and TXN id, i think some of the TXN ids and pg_clog files generated between last stop time and the next start time are missed. Did anyone observe this behavior ?? Please help ! This is critical for us. I want to recommend not to use rsync (use cp or scp instead) for production backup. Thanks VB On Tue, Sep 27, 2011 at 2:36 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Venkat Balaji wrote: Our problem is - We had mistakenly executed rsync on the running PostgreSQL data directory (production) and we did not run pg_start_backup(). Will this harm production ? can this lead to corruption ? I assume that you used rsync to copy *from* the data directory. This cannot lead to data corruption. Only performance might suffer temporarily due to the additional I/O. The backup made with rsync will be unusable without pg_start_backup(). Yours, Laurenz Albe
[GENERAL] How can i get record by data block not by sql?
How can i get record by data block not by sql? I want to read and write lots of data by data blocks, so i can form a disk-resident tree by recording the block address. But i don't know how to implement in postgresql. Is there system function can do this? Can someone help me?? Thank you very very much1
Re: [GENERAL] How can i get record by data block not by sql?
To know the page and row information : Select ctid,* from tablename; For more information at page-level, you can take the help of contrib module pageinspect. Which is under pgsql-path/share/postgresql/contrib/pageinspect.sql --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ 2011/10/3 姜头 104186...@qq.com How can i get record by data block not by sql? I want to read and write lots of data by data blocks, so i can form a disk-resident tree by recording the block address. But i don't know how to implement in postgresql. Is there system function can do this? Can someone help me?? Thank you very very much1
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
Sorry for the delay - it's been a busy morning. The Windows 7 system I'm using is a laptop with a standard basic Nokia image. To the best of my knowledge there have been no OEM modifications of any kind. It describes itself as Windows 7 Enterprise, and says it is 32-bit. That's it. Anything else you'd want me to check? Karl On Mon, Oct 3, 2011 at 4:11 AM, Dave Page dp...@pgadmin.org wrote: On Mon, Oct 3, 2011 at 8:59 AM, Magnus Hagander mag...@hagander.net wrote: On Fri, Sep 30, 2011 at 15:34, Karl Wright daddy...@gmail.com wrote: I saw a thread where somebody saw icacls.exe being called by the one-click installer. I'm having the same thing - the installer has been running for 45 minutes now and is basically going to have to be stopped because I'm out of time waiting for it. Looking at process monitor, it is clear that icacls.exe is going through every file on the entire system and changing its permissions. The process tree indicates that it is a child of the installer, and that it is running the command: icacls C:\ /grant kawright:RX Clearly this won't do at all and should be considered a severe installer bug. If it does, it certainly sounds like a very bad bug. However, according to the documentation for icacls (http://technet.microsoft.com/en-us/library/cc753525(WS.10).aspx), you should use /t to get it to traverse into subdirectories, and clearly it's not doing that. So I wonder why it would go across the whole filesystem - might tbere be a bug in icacls? Yes - that's how it's supposed to work (ie. *not* using /t). The purpose of that code is to ensure that the entire path leading up to the data/installation directories is readable by the users that need it. We've had a number of reported installation failures in the past caused by weirdness where read or execute permissions weren't available for (for example) the service account user, which caused somewhat mysterious failures. Or maybe it has something to do with inheritance? The way inheritance-permissions works on ntfs is, um, let's call it interesting. Maybe it needs to specify the (NP) flag to not propagate inheritance or something? Sachin/Ashesh; can one of you investigate this please? Karl; can you please provide precise details of your Windows version, and anything unusual about your disk configuration? I know this doesn't happen on any of the installations of Windows 7 that we use for testing (which tend to be the MSDN builds, running on local NTFS disks), so I wonder if there's an icacls bug in a specific build or rev of Windows, or when used on a certain type of filesystem. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
Thanks Karl. Is SP1 installed? Sachin, Ashesh - anything else you can think of that would be useful? On Mon, Oct 3, 2011 at 1:48 PM, Karl Wright daddy...@gmail.com wrote: Sorry for the delay - it's been a busy morning. The Windows 7 system I'm using is a laptop with a standard basic Nokia image. To the best of my knowledge there have been no OEM modifications of any kind. It describes itself as Windows 7 Enterprise, and says it is 32-bit. That's it. Anything else you'd want me to check? Karl On Mon, Oct 3, 2011 at 4:11 AM, Dave Page dp...@pgadmin.org wrote: On Mon, Oct 3, 2011 at 8:59 AM, Magnus Hagander mag...@hagander.net wrote: On Fri, Sep 30, 2011 at 15:34, Karl Wright daddy...@gmail.com wrote: I saw a thread where somebody saw icacls.exe being called by the one-click installer. I'm having the same thing - the installer has been running for 45 minutes now and is basically going to have to be stopped because I'm out of time waiting for it. Looking at process monitor, it is clear that icacls.exe is going through every file on the entire system and changing its permissions. The process tree indicates that it is a child of the installer, and that it is running the command: icacls C:\ /grant kawright:RX Clearly this won't do at all and should be considered a severe installer bug. If it does, it certainly sounds like a very bad bug. However, according to the documentation for icacls (http://technet.microsoft.com/en-us/library/cc753525(WS.10).aspx), you should use /t to get it to traverse into subdirectories, and clearly it's not doing that. So I wonder why it would go across the whole filesystem - might tbere be a bug in icacls? Yes - that's how it's supposed to work (ie. *not* using /t). The purpose of that code is to ensure that the entire path leading up to the data/installation directories is readable by the users that need it. We've had a number of reported installation failures in the past caused by weirdness where read or execute permissions weren't available for (for example) the service account user, which caused somewhat mysterious failures. Or maybe it has something to do with inheritance? The way inheritance-permissions works on ntfs is, um, let's call it interesting. Maybe it needs to specify the (NP) flag to not propagate inheritance or something? Sachin/Ashesh; can one of you investigate this please? Karl; can you please provide precise details of your Windows version, and anything unusual about your disk configuration? I know this doesn't happen on any of the installations of Windows 7 that we use for testing (which tend to be the MSDN builds, running on local NTFS disks), so I wonder if there's an icacls bug in a specific build or rev of Windows, or when used on a certain type of filesystem. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
On Mon, Oct 3, 2011 at 9:42 AM, Thomas Kellerer spam_ea...@gmx.net wrote: Dave Page wrote on 03.10.2011 10:11: Karl; can you please provide precise details of your Windows version, and anything unusual about your disk configuration? I know this doesn't happen on any of the installations of Windows 7 that we use for testing (which tend to be the MSDN builds, running on local NTFS disks), so I wonder if there's an icacls bug in a specific build or rev of Windows, or when used on a certain type of filesystem. I have reported this as well some weeks ago. For me this happened on a Windows7 64 bit system that is part of a domain (all current MS patches applied) UAC is turned off, I was logged in as a local administrator (*not* a domain administrator). I have a ProcessMonitor trace that shows the behaviour of what icacls is doing when started by the installer. It is available here: http://www.sql-workbench.net/icacls_trace.zip Thanks Thomas. That certainly doesn't look like it's doing what it was told to :-s -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
On Mon, Oct 3, 2011 at 2:39 PM, mgo...@isstrucksoftware.net wrote: I am running Windows 7 x64 (version 6.1.7601). My copy is also from the MSDN with latest updates applied. When I get to the end of the postgres install I get a error: Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed. I very much doubt that s the issue being discussed here. I'd suggest starting a new thread on pgsql-bugs and including your installation logs from %TEMP%. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company top.letterhead
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
Some errata and round up comments. First of all, I think it's relevant to give some info about the system I'm running: OS: Linux Distro: Gentoo Perl: dev-lang/perl-5.12.3-r1 USE=-* berkdb gdbm Gentoo Base System release: 2.0.2 PORTDIR/metadata/timestamp.chk: Tue, 12 Jul 2011 00:45:01 + 2011/10/2, Diego Augusto Molina diegoaugustomol...@gmail.com: solved if we use no composite at all! Instead of a field with an array of a composite of three instrinsics, use three fields, each of an intrinsic type. See your proposal: Errata: use three fields, each *one being an array of an intrinsic type.* mentioned earlier (to reduce space: 2 bytes of type smallint against variable size of type text; and to keep track of names been used too). You can also set up something like this if you like dimensions: Errata: and to keep track of names *being* used too) Implying that the first dimension is the before value and the second one is for the after value. Any of these prevents us from using composites and makes the box a little wider and simpler. Even further, I would like to keep the logging on demand: Comment on I would like to keep the logging on demand: With this I mean to log only those columns wich are part of the primary key or whose values have changed -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- 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 find freak UTF-8 character?
On Sat, Oct 01, 2011 at 11:16:06PM +0200, Leif Biberg Kristensen wrote: But thank you for the idea, I think that I will strip out at least any lrm; entities from text entered into the database. If you're getting lrm, you might want to check for ZWJ and ZWNJ code points too. They're nasty because by definition they don't display (ZW stands for Zero Width. If you ever actually have to spend time understanding how things got this way in Unicode, you will have my sympathy). By the way, is there a setting in psql that will output unprintable characters as question marks or something? Not to my knowledge, although I'd expect the terminal driver to have control over this, no? A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
I see no evidence that SP1 is installed on this machine. Karl On Mon, Oct 3, 2011 at 9:14 AM, Dave Page dp...@pgadmin.org wrote: Thanks Karl. Is SP1 installed? Sachin, Ashesh - anything else you can think of that would be useful? On Mon, Oct 3, 2011 at 1:48 PM, Karl Wright daddy...@gmail.com wrote: Sorry for the delay - it's been a busy morning. The Windows 7 system I'm using is a laptop with a standard basic Nokia image. To the best of my knowledge there have been no OEM modifications of any kind. It describes itself as Windows 7 Enterprise, and says it is 32-bit. That's it. Anything else you'd want me to check? Karl On Mon, Oct 3, 2011 at 4:11 AM, Dave Page dp...@pgadmin.org wrote: On Mon, Oct 3, 2011 at 8:59 AM, Magnus Hagander mag...@hagander.net wrote: On Fri, Sep 30, 2011 at 15:34, Karl Wright daddy...@gmail.com wrote: I saw a thread where somebody saw icacls.exe being called by the one-click installer. I'm having the same thing - the installer has been running for 45 minutes now and is basically going to have to be stopped because I'm out of time waiting for it. Looking at process monitor, it is clear that icacls.exe is going through every file on the entire system and changing its permissions. The process tree indicates that it is a child of the installer, and that it is running the command: icacls C:\ /grant kawright:RX Clearly this won't do at all and should be considered a severe installer bug. If it does, it certainly sounds like a very bad bug. However, according to the documentation for icacls (http://technet.microsoft.com/en-us/library/cc753525(WS.10).aspx), you should use /t to get it to traverse into subdirectories, and clearly it's not doing that. So I wonder why it would go across the whole filesystem - might tbere be a bug in icacls? Yes - that's how it's supposed to work (ie. *not* using /t). The purpose of that code is to ensure that the entire path leading up to the data/installation directories is readable by the users that need it. We've had a number of reported installation failures in the past caused by weirdness where read or execute permissions weren't available for (for example) the service account user, which caused somewhat mysterious failures. Or maybe it has something to do with inheritance? The way inheritance-permissions works on ntfs is, um, let's call it interesting. Maybe it needs to specify the (NP) flag to not propagate inheritance or something? Sachin/Ashesh; can one of you investigate this please? Karl; can you please provide precise details of your Windows version, and anything unusual about your disk configuration? I know this doesn't happen on any of the installations of Windows 7 that we use for testing (which tend to be the MSDN builds, running on local NTFS disks), so I wonder if there's an icacls bug in a specific build or rev of Windows, or when used on a certain type of filesystem. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Why PGSQL has no developments in the .NET area?
Hi! What usage case of Npgsql are you having performance issues? On Sun, Oct 2, 2011 at 00:32, Rohit Coder passionate_program...@hotmail.com wrote: PgSQL has just one old NPGSQL driver for .NET, which is itself sluggish. The ODBC driver works better as compared to NPGSQL, but I suspect the ODBC driver is not the right choice for ORM framework of .NET. I want to know whether there is any efficient .NET provider and is PGSQL compatible with the .NET entity framework. Regards, Rohit. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://gplus.to/franciscojunior http://fxjr.blogspot.com http://twitter.com/franciscojunior -- 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] Searching for bare letters
On Sun, Oct 02, 2011 at 05:45:48PM +0200, Reuven M. Lerner wrote: quite grateful for that. (I really hadn't ever needed to deal with such issues in the past, having worked mostly with English and Hebrew, which don't have such accent marks.) That isn't quite true about English. We have words like coöperate and naïve. The former is sometimes fixed with a hyphen instead, but the latter can't be. I think what happened is that English speakers, because we're already used to being sloppy (you can't tell what's a subjunctive in English, either, just by looking) were willing to adapt our spelling to reflect the limitations of typewriters. Also, English never really had an official standard spelling -- by the time the English were attempting to standardize seriously, there was already an American branch with its own Bossypants Official Reformer of Spelling (BORS, which in that case was Noah Webster. See G.B. Shaw for a British example). So we mostly lost the accents in standard spelling. We also lost various standard digraphs, like that in encyclopædia (which, depending on which branch of nonsense you subscribe to, can be spelled instead encyclopedia or encyclopaedia; both would have been called wrong once upon a time). As for the unaccent dictionary, I hadn't heard of it before, but just saw it now in contrib, and it looks like it might fit perfectly. I'll take a look; thanks for the suggestion. The big problem there is what someone else pointed to up-thread: in some languages, the natural thing to do is to transliterate using multiple characters. The usual example is that in German is it common to use e after a vowel to approximate the umlaut. So, ö becomes oe. Unfortunately, in Swedish this is clearly a mistake, and if you can't use the diaeresis, then you just use the undecorated character instead. The famous Swedish ship called the Götheborg cannot be transliterated as Goetheborg. Even in German, the rule is complicated, because it's not two-way: you can't spell the famous writer's name Göthe (even though Google seems to think you can). As far as I can tell, the unaccent dictionary doesn't handle the two-character case, though it sure looks like it could be extended to do it. But it doesn't seem to have a facility for differentiating based on the language of the string. I don't know whether that could be added. The upshot is that, if you need to store multilingual input and do special handling on the strings afterwards, you are wise to store the string with a language tag so that you can apply the right rules later on. See RFC 5646 (http://www.rfc-editor.org/rfc/rfc5646.txt) for some pointers. If just stripping accents is good enough for you, then the unaccent dictionary will probably be good enough. A -- Andrew Sullivan a...@crankycanuck.ca -- 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 can i get record by data block not by sql?
On Oct 3, 2011, at 3:03 AM, 姜头 wrote: I want to read and write lots of data by data blocks, so i can form a disk-resident tree by recording the block address. Then you want some kind of lower-level b-tree manager, not a SQL database, and certainly not PostgreSQL. Before you go down that path, you should seriously consider whether you really need that, rather than a higher-level solution. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Why PGSQL has no developments in the .NET area?
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [GENERAL] Why PGSQL has no developments in the .NET area?
I do not understand what you mean by the term sluggish? Every .NET driver has some overhead when you first load it, and then it is being cached into memory. I have used Npgsql and it worked great for me. If you are not satisfied with Npgsql, try Devarts connector. On Oct 2, 2011 5:33 AM, Rohit Coder passionate_program...@hotmail.com wrote: PgSQL has just one old NPGSQL driver for .NET, which is itself sluggish. The ODBC driver works better as compared to NPGSQL, but I suspect the ODBC driver is not the right choice for ORM framework of .NET. I want to know whether there is any efficient .NET provider and is PGSQL compatible with the .NET entity framework. Regards, Rohit.
[GENERAL] Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?
Hi, here is the testcase: create type mytype as (id integer, t varchar(255)); create table mytest (id serial, t1 varchar(255), t2 varchar(255)); create or replace function myfunc () returns setof mytype as $$ begin return query select id, (t1 || t2)::varchar from mytest; end;$$ language plpgsql; Now the problem is: select * from myfunc(); ERROR: structure of query does not match function result type DETAIL: Returned type text does not match expected type character varying(255) in column 2. CONTEXT: PL/pgSQL function myfunc line 2 at RETURN QUERY But the types are said to be the same: create cast (varchar as varchar(255)) without function; ERROR: source data type and target data type are the same create cast (varchar as varchar(255)) with inout; ERROR: source data type and target data type are the same This cast already exists: create cast (varchar as varchar(255)) with function pg_catalog.varchar(varchar, integer, boolean); ERROR: cast from type character varying to type character varying already exists I know, explicit cast to ::varchar(255) in the function solves this problem. But I would like to know why isn't the type conversion from unlimited varchar to varchar(255) invoked in the pl/pgsql function? Thanks in advance, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Why PGSQL has no developments in the .NET area?
On Mon, Oct 3, 2011 at 9:13 AM, mgo...@isstrucksoftware.net wrote: The Ngpsql driver isn't old. They've released updates to it all through version 8 and 9 so far as I know. We do some .Net work against Postgres and it seems to work for what we're doing. Indeed, the latest release seems to be less than a month old. It does seem the project could release news items more frequently though. ;-) Best Wishes, Chris Travers top.letterhead
Re: [GENERAL] Why PGSQL has no developments in the .NET area?
On Mon, Oct 3, 2011 at 13:46, Chris Travers chris.trav...@gmail.com wrote: On Mon, Oct 3, 2011 at 9:13 AM, mgo...@isstrucksoftware.net wrote: The Ngpsql driver isn't old. They've released updates to it all through version 8 and 9 so far as I know. We do some .Net work against Postgres and it seems to work for what we're doing. Indeed, the latest release seems to be less than a month old. It does seem the project could release news items more frequently though. ;-) Best Wishes, Chris Travers Yeah, I agree with you. Sorry for that. On the good side, we released a new beta last week. :) -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://gplus.to/franciscojunior http://fxjr.blogspot.com http://twitter.com/franciscojunior top.letterhead
Re: [GENERAL] : PostgreSQL Online Backup
On October 3, 2011 05:33:35 AM Venkat Balaji wrote: Did anyone observe this behavior ?? Please help ! This is critical for us. I want to recommend not to use rsync (use cp or scp instead) for production backup. rsync works fine. Why exactly can't the recovery find the backed up copy of 000105390076? Please post your archive_command settings, the contents of any script(s) called by that, and the recovery.conf file you're using that's having problems, as well as the complete process you followed to initiate recovery. I strongly suspect you're missing part of the process of actually saving the WAL files needed for recovery. -- 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] Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?
2011-10-03 18:12 keltezéssel, Boszormenyi Zoltan írta: Hi, here is the testcase: create type mytype as (id integer, t varchar(255)); create table mytest (id serial, t1 varchar(255), t2 varchar(255)); create or replace function myfunc () returns setof mytype as $$ begin return query select id, (t1 || t2)::varchar from mytest; end;$$ language plpgsql; Now the problem is: select * from myfunc(); ERROR: structure of query does not match function result type DETAIL: Returned type text does not match expected type character varying(255) in column 2. CONTEXT: PL/pgSQL function myfunc line 2 at RETURN QUERY But the types are said to be the same: create cast (varchar as varchar(255)) without function; ERROR: source data type and target data type are the same create cast (varchar as varchar(255)) with inout; ERROR: source data type and target data type are the same This cast already exists: create cast (varchar as varchar(255)) with function pg_catalog.varchar(varchar, integer, boolean); ERROR: cast from type character varying to type character varying already exists I know, explicit cast to ::varchar(255) in the function solves this problem. But I would like to know why isn't the type conversion from unlimited varchar to varchar(255) invoked in the pl/pgsql function? Two additions: create function myfunc1() returns setof varchar(255) as $$ begin return query select (t1 || t2)::varchar from mytest; end;$$ language plpgsql; select * from myfunc1(); myfunc1 - (0 rows) create or replace function myfunc2(out id integer, out t varchar(255)) returns setof record as $$ begin return query select mytest.id, (t1 || t2)::varchar from mytest; end;$$ language plpgsql; select * from myfunc2(); id | t +--- (0 rows) Only the conversion from anonymous record to composite type causes a problem, individual output parameters or single-value return values get the implicit cast. Thanks in advance, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?
On Oct 3, 2011, at 10:12 AM, Boszormenyi Zoltan wrote: But I would like to know why isn't the type conversion from unlimited varchar to varchar(255) invoked in the pl/pgsql function? What if t1 || t2 is longer than 255? You need to explicitly specify. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?
On 3 Oct 2011, at 18:12, Boszormenyi Zoltan wrote: Hi, here is the testcase: create type mytype as (id integer, t varchar(255)); create table mytest (id serial, t1 varchar(255), t2 varchar(255)); create or replace function myfunc () returns setof mytype as $$ begin return query select id, (t1 || t2)::varchar from mytest; end;$$ language plpgsql; Now the problem is: select * from myfunc(); ERROR: structure of query does not match function result type DETAIL: Returned type text does not match expected type character varying(255) in column 2. CONTEXT: PL/pgSQL function myfunc line 2 at RETURN QUERY Yes, of course. It's safe to cast a varchar(255) to a varchar, but the other way around it could get truncated. But the types are said to be the same: create cast (varchar as varchar(255)) without function; ERROR: source data type and target data type are the same They are the same type, but one version has a length constraint and the other does not. The above is not a safe cast without specifying what to do with varchars that contain more than 255 chars. But... you're also specifying the cast without function. create cast (varchar as varchar(255)) with inout; ERROR: source data type and target data type are the same If I understand the meaning of inout type casts correctly, this also doesn't create a safe type-cast. It doesn't prevent accidental truncating. If that's why the errors occur, they're at least a bit misleading. I can't say I have been creating casts so far, so I'm guessing a bit here. If you create a cast WITH function, does that work? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- 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] Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?
2011-10-03 19:17 keltezéssel, Scott Ribe írta: On Oct 3, 2011, at 10:12 AM, Boszormenyi Zoltan wrote: But I would like to know why isn't the type conversion from unlimited varchar to varchar(255) invoked in the pl/pgsql function? What if t1 || t2 is longer than 255? You need to explicitly specify. Yes, but then explicit casting would be needed everywhere and it's not the case. My question is more like: why the record - record type conversion doesn't try to match individual elements of the two record types? -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?
2011-10-03 19:31 keltezéssel, Boszormenyi Zoltan írta:
2011-10-03 18:12 keltezéssel, Boszormenyi Zoltan írta:
Hi,
here is the testcase:
create type mytype as (id integer, t varchar(255));
create table mytest (id serial, t1 varchar(255), t2 varchar(255));
create or replace function myfunc () returns setof mytype as $$
begin
return query select id, (t1 || t2)::varchar from mytest;
end;$$ language plpgsql;
Now the problem is:
select * from myfunc();
ERROR: structure of query does not match function result type
DETAIL: Returned type text does not match expected type character
varying(255) in column 2.
CONTEXT: PL/pgSQL function myfunc line 2 at RETURN QUERY
But the types are said to be the same:
create cast (varchar as varchar(255)) without function;
ERROR: source data type and target data type are the same
create cast (varchar as varchar(255)) with inout;
ERROR: source data type and target data type are the same
This cast already exists:
create cast (varchar as varchar(255)) with function
pg_catalog.varchar(varchar, integer,
boolean);
ERROR: cast from type character varying to type character varying already
exists
I know, explicit cast to ::varchar(255) in the function solves this problem.
But I would like to know why isn't the type conversion from unlimited varchar
to varchar(255) invoked in the pl/pgsql function?
Two additions:
create function myfunc1() returns setof varchar(255) as $$
begin
return query select (t1 || t2)::varchar from mytest;
end;$$ language plpgsql;
select * from myfunc1();
myfunc1
-
(0 rows)
create or replace function myfunc2(out id integer, out t varchar(255))
returns setof
record as $$
begin
return query select mytest.id, (t1 || t2)::varchar from mytest;
end;$$ language plpgsql;
select * from myfunc2();
id | t
+---
(0 rows)
Only the conversion from anonymous record to composite type
causes a problem, individual output parameters or single-value return
values get the implicit cast.
They actually don't. Let's add a row ensuring t1||t2 is longer than 255:
=# insert into mytest (t1, t2) values (repeat('a', 250), repeat('b', 250));
INSERT 0 1
=# select length(t1), length(t2) from mytest;
length | length
+
250 |250
(1 row)
=# select length(myfunc1) from myfunc1();
length
500
(1 row)
=# select length(t) from myfunc2();
length
500
(1 row)
So, although the functions look like they accept and would
perform the implicit type conversion, they actually do not. But:
=# select ''::varchar(3);
varchar
-
aaa
(1 row)
I would expect either the accepted type conversion implicitly
truncates or gives me a runtime error just like this below:
zozo=# insert into mytest (t1, t2) values (now()::text, '');
INSERT 0 1
zozo=# select t1::timestamp from mytest where id = 2;
t1
2011-10-03 21:23:52.423667
(1 row)
zozo=# select t1::timestamp from mytest;
ERROR: invalid input syntax for type timestamp:
aa
I forgot to report the version:
=# select version();
version
-
PostgreSQL 9.0.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.6.0
20110530
(Red Hat 4.6.0-9), 64-bit
(1 row)
This is on Fedora 15. I just checked, it's the same on 9.1.1 compiled fresh.
Of course, the explicit type conversion truncates correctly.
=# select id, length((t1 || t2)::varchar(255)) from mytest;
id | length
+
1 |255
2 | 29
(2 rows)
Now I start to think that pl/pgsql simply lacks some type checks and
should be stricter.
--
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] stored function (possible to access file system or call java program)?
Is it possible within a stored function to: 1) Access the file system (to rename a file for example) 2) Call a java program thanks J.V. -- 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] stored function (possible to access file system or call java program)?
On Mon, Oct 3, 2011 at 2:05 PM, J.V. jvsr...@gmail.com wrote: Is it possible within a stored function to: 1) Access the file system (to rename a file for example) 2) Call a java program For both of these: 1) yes, if you use an untrusted program and 2) These are dangerous because they are operations that exist outside the transactional control of the RDBMS, so it's usually better to look to other ways of doing this, like NOTIFY and LISTEN. Best Wishes, Chris Travers -- 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] stored function (possible to access file system or call java program)?
On Mon, Oct 3, 2011 at 4:05 PM, J.V. jvsr...@gmail.com wrote: Is it possible within a stored function to: 1) Access the file system (to rename a file for example) 2) Call a java program sounds like you should be using pl/sh (http://plsh.projects.postgresql.org/) or pl/java (http://pgfoundry.org/projects/pljava/). just remember, try not to get into situation where your function blocks indefinitely waiting for called program to exist. keep it short and sweet, or spawn the program off and collect the response in separate transaction. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored function (possible to access file system or call java program)?
On 10/03/11 2:12 PM, Merlin Moncure wrote: sounds like you should be using pl/sh (http://plsh.projects.postgresql.org/) or pl/java (http://pgfoundry.org/projects/pljava/). pljava is a integration nightmare I'd suggest plperlu -- 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] stored function (possible to access file system or call java program)?
On Mon, Oct 3, 2011 at 4:16 PM, John R Pierce pie...@hogranch.com wrote: On 10/03/11 2:12 PM, Merlin Moncure wrote: sounds like you should be using pl/sh (http://plsh.projects.postgresql.org/) or pl/java (http://pgfoundry.org/projects/pljava/). pljava is a integration nightmare hm -- care to comment in more detail? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored function (possible to access file system or call java program)?
On 10/03/11 2:18 PM, Merlin Moncure wrote: hm -- care to comment in more detail? I've had to custom build it for each platform I've deployed it on, and it then has dependencies on the exact JDK version and location. It currently doesn't work with JDK 6, only 1.5.It also requires custom lines be added to postgresql.conf and environment variables defined by the postgres service startup script. My first attempt to get it working with 64bit CentOS 6, Sun JDK 1.5.0_22, and PG 9.0.5 failed, although I need to start over and make sure I didn't mess up a step. The idea of dragging a whole JVM into the process context of the postgres server also bothers me, but I must say, that once you DO get it working, the developers in another division of $job were able to move some code over from Oracle pljava to Postgres pljava without any modifications at all. -- 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] stored function (possible to access file system or call java program)?
On 10/03/11 3:09 PM, Merlin Moncure wrote: libjvm.so = /usr/lib/libjvm.so (0x00cfc000) my libjvm.so is ... $ ldd /usr/pgsql-9.0/lib/pljava.so linux-vdso.so.1 = (0x7fff7bfff000) libjvm.so = /usr/java/jdk1.5.0_22/jre/lib/amd64/server/libjvm.so (0x7f79d9324000) libc.so.6 = /lib64/libc.so.6 (0x7f79d8f98000) libm.so.6 = /lib64/libm.so.6 (0x7f79d8d14000) libdl.so.2 = /lib64/libdl.so.2 (0x7f79d8b1) libpthread.so.0 = /lib64/libpthread.so.0 (0x7f79d88f2000) /lib64/ld-linux-x86-64.so.2 (0x003c5f80) $ ldd /usr/java/jdk1.5.0_22/jre/lib/amd64/server/libjvm.so linux-vdso.so.1 = (0x7fffcbfff000) libm.so.6 = /lib64/libm.so.6 (0x7f73bc596000) libdl.so.2 = /lib64/libdl.so.2 (0x7f73bc392000) libpthread.so.0 = /lib64/libpthread.so.0 (0x7f73bc174000) libc.so.6 = /lib64/libc.so.6 (0x7f73bbdf5000) /lib64/ld-linux-x86-64.so.2 (0x003c5f80) I've never heard of a Sun JRE in /usr/lib are you using (eeew) GCJ ? -- 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] stored function (possible to access file system or call java program)?
On 10/03/2011 09:50 PM, John R Pierce wrote: On 10/03/11 3:09 PM, Merlin Moncure wrote: libjvm.so = /usr/lib/libjvm.so (0x00cfc000) I've never heard of a Sun JRE in /usr/lib are you using (eeew) GCJ ? He is using... merlin@mmoncure-ubuntu:~$ ls -l /usr/lib/libjvm.so lrwxrwxrwx 1 root root 57 2011-08-12 17:13 /usr/lib/libjvm.so - /usr/lib/jvm/java-6-openjdk/jre/lib/i386/server/libjvm.so So, it is a symlink to openjdk Rodrigo -- 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] stored function (possible to access file system or call java program)?
On Mon, Oct 3, 2011 at 4:53 PM, John R Pierce pie...@hogranch.com wrote: On 10/03/11 2:18 PM, Merlin Moncure wrote: hm -- care to comment in more detail? I've had to custom build it for each platform I've deployed it on, and it then has dependencies on the exact JDK version and location. It currently doesn't work with JDK 6, only 1.5. It also requires custom lines be added to postgresql.conf and environment variables defined by the postgres service startup script. I don't remember if I did anything special to get this running (I downloaded the stock .so from pgfoundry), but this is working fine for me: merlin@mmoncure-ubuntu:~$ ldd /home/merlin/pljava/pljava.so linux-gate.so.1 = (0x002b2000) libjvm.so = /usr/lib/libjvm.so (0x00cfc000) libc.so.6 = /lib/libc.so.6 (0x007db000) libm.so.6 = /lib/libm.so.6 (0x0075f000) libdl.so.2 = /lib/libdl.so.2 (0x001b2000) libpthread.so.0 = /lib/libpthread.so.0 (0x0049a000) merlin@mmoncure-ubuntu:~$ ldd /usr/lib/libjvm.so linux-gate.so.1 = (0x0029c000) libm.so.6 = /lib/libm.so.6 (0x0033b000) libdl.so.2 = /lib/libdl.so.2 (0x0011) libpthread.so.0 = /lib/libpthread.so.0 (0x00114000) libc.so.6 = /lib/libc.so.6 (0x0012e000) merlin@mmoncure-ubuntu:~$ ls -l /usr/lib/libjvm.so lrwxrwxrwx 1 root root 57 2011-08-12 17:13 /usr/lib/libjvm.so - /usr/lib/jvm/java-6-openjdk/jre/lib/i386/server/libjvm.so also postgres startup script etc is normal -- just the custom .conf entries, etc. basically, everything was smooth as silk. it looks like a real bear to compile though if anything goes wrong, I'll admit. My first attempt to get it working with 64bit CentOS 6, Sun JDK 1.5.0_22, and PG 9.0.5 failed, although I need to start over and make sure I didn't mess up a step. that's (sun) probably the issue. everything's easier on linux i guess. The idea of dragging a whole JVM into the process context of the postgres server also bothers me, but I must say, that once you DO get it working, the developers in another division of $job were able to move some code over from Oracle pljava to Postgres pljava without any modifications at all. yeah -- understood, but especially with the new stuff coming down the pike for java 7 there are definitely advantages for java shops in going in this direction...I wouldn't go so far as to say 'integration nightmare' -- maybe 'situationally dependent difficulties' :-). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can i get record by data block not by sql?
On 03/10/11 17:03, wrote: How can i get record by data block not by sql? I want to read and write lots of data by data blocks, so i can form a disk-resident tree by recording the block address. But i don't know how to implement in postgresql. Is there system function can do this? It might be a good idea to take a step or three back and ask: Why? What are you trying to achieve? What is the goal? Is PostgreSQL the right choice? Have you looked at lower-level databases like Berkeley DB, various raw ISAM engines, etc? For that matter, if you want block-level operation, don't you really just want pread() and pwrite()? If you want to do something within the PostgreSQL engine using your own custom files to store data, you would have to do it by writing C functions as server-side extensions and calling those via SQL to access and manage your data. These functions would have to use their own separate data; they could **NOT** safely use existing postgresql data files in any way. -- Craig Ringer
Re: [GENERAL] : PostgreSQL Online Backup
The recovery is unable to find the WAL archive because, it was generated on 26th September. Whereas the backup is as taken on Oct 2nd, 2011. We deleted all the files. I do not have that WAL archive copy. The problem area - I found that a pg_clog file dated 26th Sep, 2011 is not synced (its not 256K). Thanks VB 2011/10/3 Alan Hodgson ahodg...@simkin.ca On October 3, 2011 05:33:35 AM Venkat Balaji wrote: Did anyone observe this behavior ?? Please help ! This is critical for us. I want to recommend not to use rsync (use cp or scp instead) for production backup. rsync works fine. Why exactly can't the recovery find the backed up copy of 000105390076? Please post your archive_command settings, the contents of any script(s) called by that, and the recovery.conf file you're using that's having problems, as well as the complete process you followed to initiate recovery. I strongly suspect you're missing part of the process of actually saving the WAL files needed for recovery. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
