Re: [GENERAL] Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3
Sorry for the late response. On 23/05/15 19:38, Adrian Klaver wrote: On 05/23/2015 04:16 PM, Marcos Ortiz wrote: On 23/05/15 19:09, Adrian Klaver wrote: On 05/23/2015 03:51 PM, Marcos Ortiz wrote: On 23/05/15 18:40, Adrian Klaver wrote: On 05/23/2015 03:27 PM, Marcos Ortiz wrote: Regards to all the list. First all the info about the system: O.S: CentOS 7 64 bits PostgreSQL version: SELECT version(); version -- PostgreSQL 9.2.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit (1 row) Neo4j version: 2.1.M Py2neo version: 2.0.8 Python version: python Python 2.7.5 (default, Jun 17 2014, 18:11:42) [GCC 4.8.2 20140120 (Red Hat 4.8.2-16)] on linux2 Now the problem: We are working here to integrate PostgreSQL with Neo4j through PL/Python using the py2neo module for it, and when we want to send sentences to Neo4j using port 7474, the executed code raises a SocketError [Errno 13] Permission denied. Well first in the code below, if I am following correctly, the socket is 37474. Yes, Adrian. Sorry for that, the correct port is 7474. I just was testing with higher ports to see if the error persisted. I tested the same code in a normal python script outside of PostgreSQL, and it works well, but the problem is when I use the code inside PostgreSQL with PL/Python. Second the plpythonu code is running as the postgres user, so does that user have permissions on the socket. Did you mean the socket created by Neo4j's server right? For that reason, I created a group in the system for this named supervisor, where neo4j/postgres users are members. So, if I find the socket file for Neo4j-server, changing permissions could solve the problem. Right? Not sure, but a quick search found that py2neo uses the neo4j REST API and that API has authorization parameters: http://neo4j.com/docs/stable/security-server.html Have you gone through the above? Yes, Adrian. py2neo installs a tool called neoauth, which can be used to create users with their respective passwords. For that reason, I use this way to create the graph: graph = Graph(http://neo4j:neo4j@10.8.45.136:7474/db/data;) using the user neo4j and its pass neo4j Not sure if it applies but see here: http://neo4j.com/docs/stable/rest-api-security.html When Neo4j is first installed you can authenticate with the default user neo4j and the default password neo4j. However, the default password must be changed (see the section called “User status and password changing”) before access to resources will be permitted. ... Yes, I changed the password. It seems that the problem was with SELinux. I disabled it for a moment to make a simple test y everything worked. But, like Tom said, I don´t want SELinux disabled in my systems, so I will find out the security label who is blocking this in CentOS, and enable it again. When I find it, I will send the solution to the list. Best wishes and thanks again for your time. -- Marcos Ortiz http://about.me/marcosortiz, Sr. Product Manager (Data Infrastructure) at UCI @marcosluis2186 http://twitter.com/marcosluis2186
SOLVED: [GENERAL] Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3
Regards, Adrian, Tom and all pgsql-general list. Like Tom said, the problem was with SELinux and I found the policy which caused all problems: grep denied audit.log | audit2allow #= postgresql_t == # This avc can be allowed using the boolean 'nis_enabled' allow postgresql_t ephemeral_port_t:tcp_socket name_connect; # This avc can be allowed using the boolean 'nis_enabled' allow postgresql_t unreserved_port_t:tcp_socket name_connect; I checked that boolean with: # getsebool -a | grep nis_enabled nis_enabled -- off Then, I changed it to on, and everything works well with SELinux enabled by default. Thanks again for the time and patience. On 24/05/15 19:43, Adrian Klaver wrote: On 05/24/2015 04:15 PM, Marcos Ortiz wrote: Sorry for the late response. Not sure if it applies but see here: http://neo4j.com/docs/stable/rest-api-security.html When Neo4j is first installed you can authenticate with the default user neo4j and the default password neo4j. However, the default password must be changed (see the section called “User status and password changing”) before access to resources will be permitted. ... Yes, I changed the password. It seems that the problem was with SELinux. I disabled it for a moment to make a simple test y everything worked. But, like Tom said, I don´t want SELinux disabled in my systems, so I will find out the security label who is blocking this in CentOS, and enable it again. When I find it, I will send the solution to the list. Glad you found the cause and thanks for following up. Nice to be able to close the loop on a problem. Best wishes and thanks again for your time. -- Marcos Ortiz http://about.me/marcosortiz, Sr. Product Manager (Data Infrastructure) at UCI @marcosluis2186 http://twitter.com/marcosluis2186 -- Marcos Ortiz http://about.me/marcosortiz, Sr. Product Manager (Data Infrastructure) at UCI @marcosluis2186 http://twitter.com/marcosluis2186
[GENERAL] Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3
Regards to all the list.
First all the info about the system:
O.S: CentOS 7 64 bits
PostgreSQL version:
SELECT version();
version
--
PostgreSQL 9.2.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)
Neo4j version: 2.1.M
Py2neo version: 2.0.8
Python version:
python
Python 2.7.5 (default, Jun 17 2014, 18:11:42)
[GCC 4.8.2 20140120 (Red Hat 4.8.2-16)] on linux2
Now the problem:
We are working here to integrate PostgreSQL with Neo4j through PL/Python
using the py2neo module for it, and when we want to send sentences to
Neo4j using port 7474, the executed code raises a SocketError [Errno 13]
Permission denied.
I tested the same code in a normal python script outside of PostgreSQL,
and it works well, but the problem is when I use the code inside
PostgreSQL with PL/Python.
This is the code of a simple function to search a node in the Neo4j graph:
CREATE OR REPLACE FUNCTION search_professor_node(nombre_prof text)
RETURNS text AS
$BODY$
from py2neo import Graph
from py2neo.cypher import CypherTransaction
graph = Graph(http://neo4j:neo4j@10.8.45.136:37474/db/data;)
tx = graph.cypher.begin()
statement = MATCH (pf:Professor) WHERE pf.nombre = {name} RETURN pf; ,
tx.append(statement, parameters={'name': nombre_prof})
professor= tx.commit()
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
and it can be used:
SELECT search_professor_node('Max');
The completed error is:
** Error **
ERROR: py2neo.packages.httpstream.http.SocketError: Permission denied
SQL state: XX000
Context: Traceback (most recent call last):
PL/Python function search_professor_node, line 6, in module
tx = graph.cypher.begin()
PL/Python function search_professor_node, line 666, in cypher
PL/Python function search_professor_node, line 212, in metadata
PL/Python function search_professor_node, line 257, in get
PL/Python function search_professor_node, line 965, in get
PL/Python function search_professor_node, line 942, in __get_or_head
PL/Python function search_professor_node, line 432, in submit
PL/Python function search_professor_node, line 361, in submit
PL/Python function search_professor_node
Thanks a lot for your time, and I hope to find a suitable solution for it.
--
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] Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3
On 23/05/15 18:38, Tom Lane wrote: Marcos Ortiz mlor...@uci.cu writes: O.S: CentOS 7 64 bits We are working here to integrate PostgreSQL with Neo4j through PL/Python using the py2neo module for it, and when we want to send sentences to Neo4j using port 7474, the executed code raises a SocketError [Errno 13] Permission denied. I tested the same code in a normal python script outside of PostgreSQL, and it works well, but the problem is when I use the code inside PostgreSQL with PL/Python. Probably SELinux is set up to deny random connections originating from the postgresql daemon. If disabling SELinux makes the problem go away then that's it. (I do *not* recommend that as a permanent solution, of course. You'll want to find some finer-grained change to the security policy. Don't remember enough about SELinux to know what the most likely bet is.) regards, tom lane I'm agree with you, Tom. I will find the SELinux policy to allow this, because I don't want to disable SELinux in the system. Searching in the system with getgetsebool -a | grep postgresql, this was the result: postgresql_can_rsync -- off postgresql_selinux_transmit_client_label -- off postgresql_selinux_unconfined_dbadm -- on postgresql_selinux_users_ddl -- on selinuxuser_postgresql_connect_enabled -- off -- Marcos Ortiz http://about.me/marcosortiz, Sr. Product Manager (Data Infrastructure) at UCI @marcosluis2186 http://twitter.com/marcosluis2186
Re: [GENERAL] Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3
On 23/05/15 18:40, Adrian Klaver wrote:
On 05/23/2015 03:27 PM, Marcos Ortiz wrote:
Regards to all the list.
First all the info about the system:
O.S: CentOS 7 64 bits
PostgreSQL version:
SELECT version();
version
--
PostgreSQL 9.2.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)
Neo4j version: 2.1.M
Py2neo version: 2.0.8
Python version:
python
Python 2.7.5 (default, Jun 17 2014, 18:11:42)
[GCC 4.8.2 20140120 (Red Hat 4.8.2-16)] on linux2
Now the problem:
We are working here to integrate PostgreSQL with Neo4j through PL/Python
using the py2neo module for it, and when we want to send sentences to
Neo4j using port 7474, the executed code raises a SocketError [Errno 13]
Permission denied.
Well first in the code below, if I am following correctly, the socket
is 37474.
Yes, Adrian. Sorry for that, the correct port is 7474. I just was
testing with higher ports to
see if the error persisted.
I tested the same code in a normal python script outside of PostgreSQL,
and it works well, but the problem is when I use the code inside
PostgreSQL with PL/Python.
Second the plpythonu code is running as the postgres user, so does
that user have permissions on the socket.
Did you mean the socket created by Neo4j's server right?
For that reason, I created a group in the system for this named
supervisor, where neo4j/postgres users are members.
So, if I find the socket file for Neo4j-server, changing permissions
could solve the problem. Right?
Neo4j is a Java-based platform.
This is the code of a simple function to search a node in the Neo4j
graph:
CREATE OR REPLACE FUNCTION search_professor_node(nombre_prof text)
RETURNS text AS
$BODY$
from py2neo import Graph
from py2neo.cypher import CypherTransaction
graph = Graph(http://neo4j:neo4j@10.8.45.136:37474/db/data;)
tx = graph.cypher.begin()
statement = MATCH (pf:Professor) WHERE pf.nombre = {name} RETURN pf; ,
tx.append(statement, parameters={'name': nombre_prof})
professor= tx.commit()
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
and it can be used:
SELECT search_professor_node('Max');
The completed error is:
** Error **
ERROR: py2neo.packages.httpstream.http.SocketError: Permission denied
SQL state: XX000
Context: Traceback (most recent call last):
PL/Python function search_professor_node, line 6, in module
tx = graph.cypher.begin()
PL/Python function search_professor_node, line 666, in cypher
PL/Python function search_professor_node, line 212, in metadata
PL/Python function search_professor_node, line 257, in get
PL/Python function search_professor_node, line 965, in get
PL/Python function search_professor_node, line 942, in
__get_or_head
PL/Python function search_professor_node, line 432, in submit
PL/Python function search_professor_node, line 361, in submit
PL/Python function search_professor_node
Thanks a lot for your time, and I hope to find a suitable solution
for it.
--
Marcos Ortiz http://about.me/marcosortiz, Sr. Product Manager (Data
Infrastructure) at UCI
@marcosluis2186 http://twitter.com/marcosluis2186
Re: [GENERAL] Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3
On 23/05/15 19:09, Adrian Klaver wrote:
On 05/23/2015 03:51 PM, Marcos Ortiz wrote:
On 23/05/15 18:40, Adrian Klaver wrote:
On 05/23/2015 03:27 PM, Marcos Ortiz wrote:
Regards to all the list.
First all the info about the system:
O.S: CentOS 7 64 bits
PostgreSQL version:
SELECT version();
version
--
PostgreSQL 9.2.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)
Neo4j version: 2.1.M
Py2neo version: 2.0.8
Python version:
python
Python 2.7.5 (default, Jun 17 2014, 18:11:42)
[GCC 4.8.2 20140120 (Red Hat 4.8.2-16)] on linux2
Now the problem:
We are working here to integrate PostgreSQL with Neo4j through
PL/Python
using the py2neo module for it, and when we want to send sentences to
Neo4j using port 7474, the executed code raises a SocketError
[Errno 13]
Permission denied.
Well first in the code below, if I am following correctly, the socket
is 37474.
Yes, Adrian. Sorry for that, the correct port is 7474. I just was
testing with higher ports to
see if the error persisted.
I tested the same code in a normal python script outside of
PostgreSQL,
and it works well, but the problem is when I use the code inside
PostgreSQL with PL/Python.
Second the plpythonu code is running as the postgres user, so does
that user have permissions on the socket.
Did you mean the socket created by Neo4j's server right?
For that reason, I created a group in the system for this named
supervisor, where neo4j/postgres users are members.
So, if I find the socket file for Neo4j-server, changing permissions
could solve the problem. Right?
Not sure, but a quick search found that py2neo uses the neo4j REST API
and that API has authorization parameters:
http://neo4j.com/docs/stable/security-server.html
Have you gone through the above?
Yes, Adrian. py2neo installs a tool called neoauth, which can be used to
create users with their respective passwords.
For that reason, I use this way to create the graph:
graph = Graph(http://neo4j:neo4j@10.8.45.136:7474/db/data;)
using the user neo4j and its pass neo4j
Neo4j is a Java-based platform.
This is the code of a simple function to search a node in the Neo4j
graph:
CREATE OR REPLACE FUNCTION search_professor_node(nombre_prof text)
RETURNS text AS
$BODY$
from py2neo import Graph
from py2neo.cypher import CypherTransaction
graph = Graph(http://neo4j:neo4j@10.8.45.136:37474/db/data;)
tx = graph.cypher.begin()
statement = MATCH (pf:Professor) WHERE pf.nombre = {name} RETURN
pf; ,
tx.append(statement, parameters={'name': nombre_prof})
professor= tx.commit()
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
and it can be used:
SELECT search_professor_node('Max');
The completed error is:
** Error **
ERROR: py2neo.packages.httpstream.http.SocketError: Permission denied
SQL state: XX000
Context: Traceback (most recent call last):
PL/Python function search_professor_node, line 6, in module
tx = graph.cypher.begin()
PL/Python function search_professor_node, line 666, in cypher
PL/Python function search_professor_node, line 212, in metadata
PL/Python function search_professor_node, line 257, in get
PL/Python function search_professor_node, line 965, in get
PL/Python function search_professor_node, line 942, in
__get_or_head
PL/Python function search_professor_node, line 432, in submit
PL/Python function search_professor_node, line 361, in submit
PL/Python function search_professor_node
Thanks a lot for your time, and I hope to find a suitable solution
for it.
--
Marcos Ortiz http://about.me/marcosortiz, Sr. Product Manager (Data
Infrastructure) at UCI
@marcosluis2186 http://twitter.com/marcosluis2186
--
Marcos Ortiz http://about.me/marcosortiz, Sr. Product Manager (Data
Infrastructure) at UCI
@marcosluis2186 http://twitter.com/marcosluis2186
[GENERAL] pgdump (9.2.4) not dumping all tables
hello im trying to dump a complete DB, i've been doing something like this. (i'm in the process of upgrading from 9.2.4 to 9.3.5) my current DB looks like this: Name| Owner | Encoding | Collate | Ctype | Access privileges ---+--+---+-+---+--- DB | postgres | UTF8 | en_US.UTF-8 | C | =Tc/postgres + | | | || postgres=CTc/postgres+ | | | | | jp=CTc/postgres having 171 tables my dump has been done with this: 0) su postgres -c /usr/local/pgsql9.2.4/bin/pg_dump -p 5433 -Fc -v $db $backup_path/$db.bkp 1) then i create the DB (i postgis enable it) 2) and then do the restore with a postgis perl script su postgres -c export PATH=/usr/local/pgsql9.3.5/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games perl /dls/gis/postgis-2.1.3/utils/postgis_restore.pl $baclup_file | /usr/local/pgsql9.3.5/bin/psql -h localhost -p 5432 -U postgres $db 2 $backup_path/$db_upgrade_errors.txt everything seems to work fine until i noticed that i'm missing 5 tables, doing a diff on two files i find out which 5 tables are missing, there is nothing special about this tables except that I noticed some empty fields , like this: id | x | y | name| placetype | point_geom --+---+---+---+---+ 1 | | | Km. 223123 RN-09 | 1 | 010120E610F75BB76C0C1A57DCasdaas2F40 2 | | | Km. 223120 RN-09 | 1 | 010120E610ECFCasdasdasd1D3FC122F40 then i try to do an individual dump of the missing tables (which is not so tedious giving the fact that it are only 5 tables) but when i see the dump file those empty fields are translated to something like this : COPY al_shared_place (id, x, y, name, placetype, point_geom) FROM stdin; 1 *\N \N * Km. 223123 RN-09 1 010120E610F75BB76C0C1A57DCasdaas2F40 2 *\N \N * Km. 223120 RN-09 1 010120E610ECFCasdasdasd1D3FC122F40 so when i do the restore of the table SET SET SET SET SET SET ERROR: relation al_shared_place does not exist invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N thanks for your help. NOTE: is important to mention that my DB is a postgis enable DB, i don't think this is the issue that's why im asking here because i think its more an encoding missmatch or something like that. -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736.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] pgdump (9.2.4) not dumping all tables
might be awkard but there is no file, so i assume 2 things: 1) there was no upgrade errors 2) the script + command is not writing to stderr (i think it is doing it) -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827739.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] pgdump (9.2.4) not dumping all tables
i did it again... and the file shows exactly the same lots of : invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827746.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] pgdump (9.2.4) not dumping all tables
to answer to Adrian
1) i am using the old version to dump (i will try with the latest)
2) no the postgis version is different. pg9.2.4- postgis-2.0.3 and
pg9.3.5- postgis-2.1.3
3) the schema is public
\d+ al_shared_place
Table
public.al_shared_place
Column | Type | Modifiers
| Storage | Stats target | Description
+---+---+--+--+-
id | integer | not null default
nextval(('al_landmark_seq'::text)::regclass) | plain| |
x | character varying(15) |
| extended | |
y | character varying(15) |
| extended | |
name | character varying(50) |
| extended | |
placetype | integer |
| plain| |
point_geom | geometry |
| main | |
Indexes:
al_shared_place_pkey PRIMARY KEY, btree (id)
al_sharedplace_geom_idx gist (point_geom)
Check constraints:
$1 CHECK (srid(point_geom) = 4326)
$2 CHECK (geometrytype(point_geom) = 'POINT'::text OR point_geom IS
NULL)
Has OIDs: no
--
View this message in context:
http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827747.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] pgdump (9.2.4) not dumping all tables
i found this in the file... ERROR: could not access file $libdir/rtpostgis-2.0: No such file or directory which acording to the firs link I found http://gis.stackexchange.com/questions/75520/missing-libraries-when-upgrading-to-postgis-2-1-and-postgresql-9-3-1-using-homeb , is a postgis bug.. i don't know if that is the reason though. but i guess so. :/ -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827750.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] pgdump (9.2.4) not dumping all tables
yes i'd better look at the postgis list.. i thought for one moment that this was encoding related. thanks for your help -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827760.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] pgdump (9.2.4) not dumping all tables
so i fix it and got it working !!! i followed the best practices of doing the dump with the newest pg_dump version. and now is working thanks everyone for your help -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827821.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] using trigger to change statusin one table from the modification in other table
Hello guys, i'm trying to use trigger in these two tables below, in order to do the following; To cancel the booking of an accommodation, since the customer do the host (status = Cancelled) in the book_rooms table. Changing the state of accommodation to occupied (Accommodation Table). CREATE TABLE book_rooms (Idbookroom int AUTO_INCREMENT not null, IdHost int not null, IdAccommododation int not null, booking_date Date not null, InitialDate Date not null, EndDate not null, Status varchar(10) not null check (Estado in (‘active’, ‘Cancelled’)), PRIMARY KEY (Idbookroom), FOREIGN KEY (IdHost) REFERENCES Hosts(IdHost), FOREIGN KEY (IdAccommododation) REFERENCES Accommodations(IdAccommododation), UNIQUE(IdHost, IdAccommododation, booking_date)) TABLE Accommodations (IdAccommododation int AUTO_INCREMENT not null, name varchar(20) not null, high_season_price not null numeric (5,2), low_season_price not null numeric (5,2), Status varchar(12) not null ((status = 'occupied') or (estado = 'available') or (estado = 'maintenance')), PRIMARY KEY (IdAccommododation), UNIQUE(name)), can someone help will be appreciated -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using trigger to change status in one table from the modification in other table
Hello guys, i'm trying to use trigger in these two tables below, in order to do the following; To cancel the booking of an accommodation, since the customer do the host (status = Cancelled) in the book_rooms table. Changing the state of accommodation to occupied (Accommodation Table). CREATE TABLE book_rooms (Idbookroom int AUTO_INCREMENT not null, IdHost int not null, IdAccommododation int not null, booking_date Date not null, InitialDate Date not null, EndDate not null, Status varchar(10) not null check (Estado in (‘active’, ‘Cancelled’)), PRIMARY KEY (Idbookroom), FOREIGN KEY (IdHost) REFERENCES Hosts(IdHost), FOREIGN KEY (IdAccommododation) REFERENCES Accommodations(IdAccommododation), UNIQUE(IdHost, IdAccommododation, booking_date)) TABLE Accommodations (IdAccommododation int AUTO_INCREMENT not null, name varchar(20) not null, high_season_price not null numeric (5,2), low_season_price not null numeric (5,2), Status varchar(12) not null ((status = 'occupied') or (estado = 'available') or (estado = 'maintenance')), PRIMARY KEY (IdAccommododation), UNIQUE(name)), Any help will be appreciated -- 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] function on trigger
On Aug 31, 7:39 pm, Marcos Hercules Santos mhe...@gmail.com wrote: hi guys I'm newbie in Psql and I'm trying to build one function in order to count the products for each supplier. So i'm gonna put it quite simply though this example Please, consider a table called books with the following fields bookid, title, price, idPublisher and one another table called publisher Idpublisher, name, city, Books Being in that last field from Publisher, called book, I gotta have the amount of published books for each publisher. sorry guys, the last field in the publisher table is for the amount of books published by each Publisher Idpublisher, name, city, Books, amount_Books_ Guys, I'm discarding creating any view. I already have the query to count the data, but my problem is how to insert this data in the amount_books_ using function... thank goodness for the reply -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] function on trigger
hi guys I'm newbie in Psql and I'm trying to build one function in order to count the products for each supplier. So i'm gonna put it quite simply though this example Please, consider a table called books with the following fields bookid, title, price, idPublisher and one another table called publisher Idpublisher, name, city, Books Being in that last field from Publisher, called book, I gotta have the amount of published books for each publisher. -- 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] default grant
Sim Zacks escribió:
I forgot to mention I'm using 8.2
On 4/7/2010 1:40 PM, Sim Zacks wrote:
Is there a way to grant permissions on any new objects (or at least new
tables) to a role?
In my app I have a function that drops and recreates a table at certain
times (with a different structure). Like a temporary table, but it
survives until a process is run which overwrites it. The process is not
the same user as the user who wants to view the table.
As soon as this happens the users lose rights to the table and I need to
manually run:
Grant select on NEWTABLE for users;
Thanks
Sim
Are you use the 8.4 version?
On that version the GRANT command have many useful changes.
The syntax is the following:
All grant syntax for privileges follow the same general structure
GRANT { { PRIVILEGES } [,...] | ALL [ PRIVILEGES ] }
– ON { OBJECT TYPE } object [, ...]
– TO { [ GROUP ] rolename | PUBLIC } [, ...]
– [ WITH GRANT OPTION ]
• The default 'object type' is TABLE
• Functions include the argument types in the 'object'
• PRIVILEGES are only those which are applicable to the object type
being modified
• The GROUP keyword is only for backwards compatibility
• WITH GRANT OPTION is included then the grantee will be able to
grant the same privilege to others
• Granting to PUBLIC gives all users the privilege
That text is on the excellent presentation on the PgCon2009 by Stephen
Frost called PostgreSQL Access Control (AuthN, AuthZ, Perms)
Other excellent change is the per column permision. For example:
To illustrate a bit more clearly:
• GRANT SELECT (col2, col3) ON mytable TO role1;
– Grants select on columns “col2” and “col3” in table “mytable” to “role1”
• GRANT INSERT(col1), UPDATE (col2) ON mytable TO role2;
– Grants insert on “col1”, update on “col2” in table “mytable” to “role2”
• GRANT SELECT, UPDATE(col3) ON mytable TO role3;
– Grants select on the table, and update on “col3” for “mytable” to “role3”
My advice is that you have to see that presentation.
Regards,
--
-- Ing. Marcos Luís Ortíz Valmaseda --
-- Linux User # 418229--
-- System Database Architect/Administrator--
-- *** PostgreSQL ***--
-- http://www.postgresql.org --
-- http://planetpostgresql.org--
-- http://www.postgresql-es.org --
-- http://commitfest.postgresql.org --
-- http://developer.postgresql.org--
-- http://www.enterprisedb.com/tservices/certification
-- *** Contacto *** --
-- Twitter: http://twitter.com/@marcosluis2186--
-- Facebook: http://www.facebook.com/marcosluis2186 --
-- GTalk: marcosluis2...@gmail.com--
-- Ruby on Rails Fan/Developer--
-- http://rubyonrails.org --
-- http://www.37signals.com --
-- Comunidad Técnica Cubana de PostgreSQL --
-- http://postgresql.uci.cu --
-- http://personas.grm.uci.cu/+marcos --
-- Centro de Tecnologías de Gestión de Datos (DATEC) --
-- Contacto:--
-- Correo: da...@uci.cu --
-- Telf: +53 07-837-3737--
-- +53 07-837-3714--
-- Universidad de las Ciencias Informáticas --
-- http://www.uci.cu --
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [PERFORM] [GENERAL] PostgreSQL - case studies
,Heartbeat for monitoring and fault detections and CentOS nd FreeBSD like OS base. The pg_xlog directory are in a RAID-1 and the main data in a RAID-10. Do you have any recommendation? Note: Any has a MPP querys implementation for PostgreSQL that can be shared? Regards -- Para ser realmente grande, hay que estar con la gente, no por encima de ella. Montesquieu Ing. Marcos Luís Ortíz Valmaseda PostgreSQL System DBA DWH -- BI Apprentice Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) Universidad de las Ciencias Informáticas Linux User # 418229 -- PostgreSQL -- TIP 4: No hagas 'kill -9' a postmaster http://www.postgresql-es.org http://www.postgresql.org http://www.planetpostgresql.org -- DWH + BI -- The Data WareHousing Institute http://www.tdwi.org http://www.tdwi.org/cbip - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [PERFORM] [GENERAL] Strange performance degradation
Lorenzo Allegrucci escribió: Matthew Wakeling wrote: On Mon, 23 Nov 2009, Lorenzo Allegrucci wrote: Anyway, how can I get rid those idle in transaction processes? Can I just kill -15 them or is there a less drastic way to do it? Are you crazy? Sure, if you want to destroy all of the changes made to the database in that transaction and thoroughly confuse the client application, you can send a TERM signal to a backend, but the consequences to your data are on your own head. I'm not crazy, it was just a question.. Anyway, problem solved in the Django application. Matthew replied to you of that way because this is not a good manner to do this, not fot thr fact that you are crazy. You can find better ways to do this. Regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Debugger install
Hello All, I did install the pgsql 8.3.7 on Ubuntu 8.04 using apt-get, now i need to install pldebugger (edb-debugger) to write complex stored procedures. The problem is i didn't use the source code to install the database and the instructions found in the documentation is using postgres source code. how can i do to instal this utility without the source code? ps. i'm not an Linux expert nor a DBA, im an application developer! Tks Marcos Davi Reis
[GENERAL] PL-Debugger installation problem
Hello All, I did install the pgsql 8.3.7 on Ubuntu 8.04 using apt-get, now i need to install pldebugger (edb-debugger) to write complex stored procedures. The problem is i didn't use the source code to install the database and the instructions found in the documentation is using postgres source code. how can i instal this utility without the source code? ps. i'm not a Linux expert nor a DBA, im an application developer! tks, Marcos Davi Reis
[GENERAL] [HACKERS] exit
I would like to leave the list ... -- Acelerador POP Acelere a sua conexo discada em at 19 x. Use o Acelerador POP. grtis, pegue j o seu. http://www.pop.com.br/acelerador
Re: [GENERAL] Turn OFF Stats of Postgresql
Hi Ycrux Thank very much for your help. Marcos Em Ter, 2006-03-14 às 21:28 +0100, Ycrux escreveu: Ho Marcos! You can also try to adpat this parameters to your config: TUNING: * First, see: http://www.lyris.com/lm_help/6.0/tuning_postgresql.html * Adjust this params in your /etc/sysctl.conf to obtain the best performances: kernel.shmmax=7000 kernel.shmall=1350 net.core.rmem_max=8388608 net.core.rmem_default=65536 net.core.wmem_max=8388608 net.core.wmem_default=65536 * Adapt and copy the postgresql.conf to your /var/pgsql/data directory. Younes Marcos a écrit : Hi, I have a search engine and for increase the performance I want to turn off the stats of Postgresql but i don't know if i should do it. Should I make this? Thanks. Marcos ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Turn OFF Stats of Postgresql
Hi, I have a search engine and for increase the performance I want to turn off the stats of Postgresql but i don't know if i should do it. Should I make this? Thanks. Marcos ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Take advantage of PREPARE (Pool of Conections)
Hi, I will have an aplication on the web that will have many connections because it will be looking for data. I'm doing the main functions that will be used in PL/PGSQL, they use the PREPARE resource, but in order to use it, I need the connection to be permanent because the PLAN generated by PREPARE only exists while the connection is opened. This means, if each visitor of the website opens and closes the connection, PREPARE is useless. I need the connection pool for POSTGRESQL for Linux, but i don't know how to do this. Thanks in advance. Marcos. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Take advantage of PREPARE (Pool of Conections)
It's called pgpool. I've installed it in my computer, that's o.k. Now, how do I to know if it's working with Postgresql? Thanks :o) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Tsearch 2
Hi, How the tsearch2 work? I can use it in my project that is language Português (Brasil)? Thanks. Marcos. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] What is made a mistake with SP?
Hi, I need create SP that returns cursos so that I can work with them. I have many SQLs used for search records in database, then I will make SP to return the results. The example that I'm trying is: CREATE OR REPLACE FUNCTION fun_compras_calculado() RETURNS SETOF tipo_compras_calculado AS ' DECLARE linha_comprasRECORD; linha_calculada tipo_compras_calculado; BEGIN linha_calculada.acumulado := 0; FOR linha_compras IN SELECT id, qtd, produto, unitario FROM compras ORDER BY id LOOP linha_calculada.id := linha_compras.id; linha_calculada.produto := linha_compras.produto; linha_calculada.qtd := linha_compras.qtd; linha_calculada.unitario := linha_compras.unitario; linha_calculada.valor := linha_compras.qtd * linha_compras.unitario; linha_calculada.acumulado := linha_calculada.acumulado + linha_calculada.valor; RETURN NEXT linha_calculada; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What is made a mistake with SP?
Hi John Thanks for response. In other words, what does select * from fun_compras_calculado(); return and if it is not an error, why is it wrong? My problem is in CREATE the function, see: [EMAIL PROTECTED] psql teste -U teste teste.sql ERRO: tipo tipo_compras_calculado não existe In english I think that it means ERROR: type tipo_compras_calculado not exists. This is my problem with this SP... Marcos. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Create Produre for DROP row
BEGIN DELETE FROM . EXCEPTION WHEN others THEN ... END; documentation: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING a list of errors: http://www.postgresql.org/docs/current/static/errcodes-appendix.html -- regards, Thanks. When I use the EXCEPTION and I return a value numeric or text the Postgresql shows a error of encoding :(... Invalid Encoding UTF-8 at... This error only occurs when a EXCEPTION treated by me it's raised. My database is in UTF8 encoding. What's happen? E.g: EXCEPTION WHEN others THEN RETURN 'Error'; Marcos. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Encoding Problem
Hi. I'm receiving this error message: invalid UTF-8 byte sequence detected SET search_path TO administracao,public; CREATE OR REPLACE FUNCTION inserirTela(text,text,text) RETURNS text AS ' DECLARE sName ALIAS FOR $1; sDesc ALIAS FOR $2; cSist ALIAS FOR $3; BEGIN SET search_path TO administracao,public; INSERT INTO telas (nome_tela,descricao,sistema) VALUES( sName, sDesc, cSist ); RETURN ''Registro inserido com sucesso''; EXCEPTION WHEN unique_violation THEN RETURN ''Ja existe uma tela com este nome''; WHEN others THEN RETURN ''Erro ao inserir registro''; END; ' LANGUAGE plpgsql; The problem is in the RETURN messages... if it's oculted the error not occur. What i do? My database is UTF-8. Thanks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Concept about stored procedures
Hi, I'm developing one application using this wonderful Database, and I've like of use the concepts correctly. Then, i decided that all my SQL statements will be in database using the procedure language (plpgsql or plpython), I go create functions for all interactions in database, and use in the my application the SELECT for call him. Is its the correct way of working? Or the correct way is create procedure functions for big and complex statements? The application will work with a large Database and will have many simultaneous access. Very Thanks ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Create Produre for DROP row
Hi, Does have possible create one procedure to delete a row that return TRUE if the deletion was success or FALSE if a error occur? The procedure for deletion I already create but I don't know how detect if the deletion was success executed. Languagel: plpgsql Thanks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Performance Low Using the Prepare and Execute
Hi, I want to use the Prepare and Execute resources of PostgreSQL to increment the performance of my SQL's. I do tests using the PHP and ADODB inserting 5000 registers and counting the time of execution. But the results was pratically identical. See below the results: Time of Execution (ADODB:: Prepare and Execute Manually) : 44.9510087967 Time of Execution (ADODB:: Prepare and Execute of ADODB): 47.6438999176 Time of Execution (ADODB:: Without Prepare): 47.6229438782 Test1 Code: $sSQL = 'EXECUTE teste(\'teste0...\')'; for ( $i = 0; $i $iNTestes; $i++ ) $oDB-execute_query( $sSQL, __LINE__, __FILE__ ); Test2 Code: $sSQL = 'INSERT INTO teste (nome) VALUES( ? )'; $oDB-Prepare( $sSQL ); for ( $i = 0; $i $iNTestes; $i++ ) $oDB-ExecutePrepare( Array( 'teste1...' ), __LINE__, __FILE__ ); Teste3 Code: $sSQL = 'INSERT INTO teste (nome) VALUES(\'teste2...\')'; for ( $i = 0; $i $iNTestes; $i++ ) $bOk = $oDB-execute_query( $sSQL, __LINE__, __FILE__ ); The SQL that they use the Prepare and Execute would have to be lesser than the others? Thanks. -- __ Marcos José Setim [EMAIL PROTECTED] http://www.linuxhard.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Create one prepared function
Hi, I'd like that create functions in plpgsql with prepared SQL and plan saved, to that the Postgresl increase the performance of executions. This is possible? I would like to see a simple example of use this, if this is possible, i find for examples in the google, but the joined result is little direct and many dispersed. Very Thanks!! -- __ Marcos José Setim [EMAIL PROTECTED] http://www.linuxhard.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [ANNOUNCE] 8.1 Beta 3: The Final Beta?
Please unsubscribe my e-mail [EMAIL PROTECTED] thank you --- Marc G. Fournier [EMAIL PROTECTED] escreveu: Like all of our beta periods, this one has been going smoothly ... but, unlike other ones, we're seeing most bugs found very early in the cycle, so right now its looking like a total of 3 Betas might be all that it takes to get us to the Release. With that in mind, we have just released our 3rd, and hopefully final, Beta of 8.1, and, as with all betas, we need to see as much testing as possible with this one, and any bugs reported. PostgreSQL 8.1 Beta 3 is now available via: http://www.postgresql.org/ftp/source/v8.1beta Thanks to Dave, the Windows version is also available via: http://www.postgresql.org/ftp/binary/v8.1beta/win32 Please report any bugs with this beta to: pgsql-bugs@postgresql.org Or through our Bug Reporting Tool available at: http://www.postgresql.org/support/submitbug Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ___ Promoção Yahoo! Acesso Grátis: a cada hora navegada você acumula cupons e concorre a mais de 500 prêmios! Participe! http://yahoo.fbiz.com.br/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] create user
hi, i have just created a new database. I would like to add, now, a new user. The user can make any changes in THIS database (no one else). i used: (from NEW database) create user new_user with password "new_pass"; i try to connect to another database with this new_user and i did it. how can i prevent this option? best regards, marcos [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] showing X fields from X total
hi, i have posgresql 6.4 and jdbc6.4. i am making a query returning like 6000 registers. I would like to restrict the query in order to show the result 100 by 100 registers. how can i make that SQL query? if i make " select * from files order by (name)"; thanks in advance, marcos -- m a r c o s @ i v a l . e s
[GENERAL] exporting table to txt
hi, i would like to make some changes in the configuration of the fields from a table so my first doubt is concerning to keep the info safe. how can i export data from my table into a text file separated with tabs? thanks in advance, marcos -- m a r c o s @ i v a l . e s
[GENERAL] Apache+Postgresql (mod_auth_pgsql)
hi, i have an apache private directory and i would like to manage the userpass from a postgresql database. i think there is something like mod_auth_pgsql but i do not know how can i use it. (i do not have mysql) this is the current configuration: Directory "/www/privado" AuthName "ZONA RESTRINGIDA" AuthType Basic AuthUserFile /www/passwd.file # AuthGroupFile /usr/local/apache/group.web require valid-user Options FollowSymLinks Includes AllowOverride Options AuthConfig Order allow,deny Allow from all ErrorDocument 401 /error.htm ErrorDocument 403 /error.htm ErrorDocument 404 /www/error.htm /Directory Firstly, how can i change this configuration in orden to allow connections to a database to verify userpassword. How many fields could the database have? (login,pass,city,) Secondly, how can i have stadistics of how many times an users has got into the privated directory. (any suggestion) Perhaps could be a script to write into the database. ?¿? Thirdly, Any suggestions will be pleased, thanks in advance, marcos [EMAIL PROTECTED]
[GENERAL] auto increment
hi, i am creating a table and i want to add an auto incrementable field is that correct? psql ival EOF create table partes ( codigo int not null auto_increment, usuario varchar(15), fecha date, proyecto varchar(30), horas int4, trabajo varchar(100), observaciones varchar(90), primary key(codigo)); EOF how can i do it? thanks in advance, marcos -- m a r c o s @ i v a l . e s
[GENERAL] NullPointerException [MORE]
hi, it occurss me with each field of the database. i have txt file. i make a database and put it into posgres. maybe it is a mistake in the TAB in the txt file. marcos [EMAIL PROTECTED]
[GENERAL] Disable the more command
Hi, Can someone tell me how can I disable the more command at the end of any one page display in PostgreSQL 7.0 ? I simply don't want to be asked to press a key to continue displaying the result of a query. Thanks a lot! Marcos Castro __ Do You Yahoo!? Get Yahoo! Mail Free email you can access from anywhere! http://mail.yahoo.com/
[GENERAL] DateStyle is Postgres with US (NonEuropean) conventions
hi list, i have start postgresql doingpostmaster -i and this is what i am getting in the shell. NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions and continuos. does anyone know how can start postgres to prevent from this? thanks. marcos [EMAIL PROTECTED]
[GENERAL] NOTICE: DateStyle is Postgres with Eropean convention
hi list, i have restart postgresql with postmaster -i -o -e and every time the servlets (via web) are accesing to posgres database it shows, in the shell, this: NOTICE: DateStyle is Postgres with European conventions thanks marcos [EMAIL PROTECTED]
[GENERAL] Cursor Problems
Hi, I have the following situation: I AM USING THE C LANGUAGE INTERFACE (LIBPQ) but I have also tried the situation described below from within psql and got the same results. I am using PostgreSQL version 6.4. - I Have to keep a cursor for a select * from table1 open most of the time and sometimes I have to insert into or update table1 - Other users may also open a cursor for a select * from table1 and may also insert into or update table1 concurrently - Is there a way to do that using cursors? In my experiments this is what happened: User 1 -- 1 - BEGIN TRANSACTION; 2 - DECLARE c1 CURSOR FOR SELECT * FROM table1; 3 - INSERT INTO table1 VALUES (...); At this time User 2 connects to the same Database and does: User 2 -- 1 - BEGIN TRANSACTION; 2 - DECLARE c1 CURSOR FOR SELECT * FROM table1; - At this time the statement does not complete and stalls the psql shell until User 1 issues a COMMIT TRANSACTION; Is there a way to either return an error code and not keep User 2 waiting undefinitely for User 1 to issue a COMMIT TRANSACTION or complete the DECLARE statement with only the tuples that already exist in the table? Where can I find more info about this sort of operation ? Thanks a lot! Marcos Castro email: [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Photos -- now, 100 FREE prints! http://photos.yahoo.com
[GENERAL] existing but not-existing database.
hi, i created a database called todojamon. (i wanted to make up of an old database trying to copy the same directoy to ../base/todojamon as before). but it does not work. i deleted the directory. now i want to restart the procedure creating a new database called todojamon. but it answers psql todojamon FATAL 1: Database 'todojamon' does not exist. We know this because the directory '/usr/local/pgsql/data/base/todojamon' does not exist. You can create a database with the SQL command CREATE DATABASE. To see what databases exist, look at the subdirectories of '/usr/local/pgsql/data/base/'. Connection to database 'todojamon' failed. FATAL 1: Database 'todojamon' does not exist. We know this because the directory '/usr/local/pgsql/data/base/todojamon' does not exist. You can create a database with the SQL command CREATE DATABASE. To see what databases exist, look at the subdirectories of '/usr/local/pgsql/data/base/'. if psql template1 [inside postgres] template1= create database todojamon; ERROR: createdb: database 'todojamon' already exists ERROR: createdb: database 'todojamon' already exists how can i delete todojamon database?? thanks in advance. marcos [EMAIL PROTECTED]
[GENERAL] troubles installing postgresql6.4
hi, i do not know what superuser account i made. so i am not able to do initdb.. it says.. We are initializing the database system with username X (uid=500). This user will own all the files and must also own the server process. mkdir: cannot create directory `/usr/local/pgsql/data/base/template1': Relaying denied. Creating template database in /usr/local/pgsql/data/base/template1 ERROR: cannot create pg_proc ERROR: cannot create pg_proc initdb: could not create template database initdb: cleaning up by wiping out /usr/local/pgsql/data/base/template1 what should i do thanks in advance, marcos [EMAIL PROTECTED]
[GENERAL] Questions about CURSORS
Hi, Suppose there is a table that's being shared among 5 computers at the same time. Suppose I've created a CURSOR for a SELECT * FROM that table GROUP BY column1. Suppose I have fetched the 3rd record and am going to fetch the 4th and in the meantime someone at another computer just DELETED that 4th record from that table.Will the fetch succeed? Will the record be shown although it no longer exists in the table? And if someone at another computer had inserted a new record which, according to my GROUP BY clause, would be the 4th, would it be shown for the next fetch? My big questions are: Do CURSORS perform their SELECT operations in the TABLE directly or in a file? Any changes to rows selected through a CURSOR will be shown right away or they will only appear as I perform another SELECT? Is there a book that I could read in order to get a better knowledge on SQL implementation or even a website where I could read about this? __ Do You Yahoo!? Kick off your party with Yahoo! Invites. http://invites.yahoo.com/
[GENERAL] Limits on PostgreSQL
Hi, I am developing an application that will use the C API for postgreSQL and I need to know this: 1 - How many tuples can be returned as a result of a query using a CURSOR? (Is it possible to do a SELECT * on a table that has 2 million records and OPEN a CURSOR for that SELECT and show all records'contents using FETCH FORWARD, for example?) 2 - When one uses a CURSOR for a SELECT is there a big memory consumption or there is a memory buffer limit and beyond that the result is written to a file (virtual memory) and read from there when needed? Marcos Castro email: [EMAIL PROTECTED] __ Do You Yahoo!? Kick off your party with Yahoo! Invites. http://invites.yahoo.com/
[GENERAL] Queries using the C API
Hi, I am new to Postgresql and to SQL as well and I have the following situation, which has been performed so far using Codebase Database Manager (xbase standard) in several application softwares, using C programming language: I have a table with 50.000 records; I have to display a grid (browse) with all the records on the table and the user has to be able to scroll up and down the grid (browse), browsing through the records on both directions (up and down). Using Codebase Database Manager, all I have to do is open the table (the file handle already points to the first record), display the current record's contents, skip to the next record, display its contents and repeat the operation until all records within the available window are displayed, leaving the file handle pointing to the first displayed record and displaying it again in a different color to show that it is the current selected record. If the user presses the down arrow key, all I have to do is skip to the next record using the Codebase skip function and then display the record in the selected color. If the user then presses the up arrow key, all I have to do is use the skip function again, passing an argument of -1 to skip 1 record backwards and display the record. Very simple indeed, isn't it? And the best part is that the Codebase Database Manager does all the dirty work through its skip function. My questions are: Is there a skip function in Postgresql that I can use to browse through the records in a table forwards and backwards (That table has 50,000 records)? If not, how would I be able to develop such a mechanism as the one explained above? Are there examples of existing algorithms or even functions, libraries of functions fit for the job? Are there examples of free software developed using the Postgresql C API? If so, where can I find them? Thanks a lot for any help on this matter. Marcos Barreto de Castro email: [EMAIL PROTECTED] phone: +55 61 3498747 fax..: +55 61 3498747 __ Do You Yahoo!? Send instant messages get email alerts with Yahoo! Messenger. http://im.yahoo.com/
