Re: [GENERAL] Memcached for Database server
On 20/05/11 13:00, Adarsh Sharma wrote: Thanks Craig, I spend some time on Memcahced and your explaination also helps. I think it is used only for applications where load is very high users issue read only queries to the database. Er, yes. You can't really cache queries that write to the database, and it doesn't make sense to cache queries where the answers change for every query. A cache is only useful where the same query returns the same result (for a while, at least) and is executed very, very often. Can you give the Real Example where memcached is used heavily. Google can. You'll need to build your independent research skills if you're going to succeed at what you're doing; it will not work well for you to rely on others making all the effort to explain everything to you. You will usually get better results when you ask people for help if you can show them that you have already made an effort to discover the answer for yourself. fore.g How Facebook, You tube use it . Five seconds on Google found: http://www.facebook.com/note.php?note_id=39391378919 -- Craig Ringer -- 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] Memcached for Database server
Please reply to the list, not directly to me.
My reply follows.
On 20/05/11 14:47, Adarsh Sharma wrote:
But Sometimes when I got stucked , I have no other option. Like I
stucked from the past 4 days to solve the attached problem.
Sure. Sometimes you get stuck, and that's what mailing lists are great
for. All I was trying to say is that the more effort you can show people
you've put in _before_ asking for help and the more effort you put into
writing a clear and complete question when you do ask for help, the more
effort they'll usually put into helping you.
The problem is Can we create and populate a dynamic table A from another
table B where
table A column names are values of one column of table B
Table A column values are the values of 2nd column of table B.
I will do it if I got the architecture to achieve this.
You're trying to transform an EAV (Entity-Attribute-Value) schema,
otherwise known as a key/value schema, into a real relational schema.
http://en.wikipedia.org/wiki/Entity-attribute-value_model
Like many relational databases, PostgreSQL isn't very well suited to
that, because it expects relations (tables) to have a fairly fixed set
of columns. It doesn't deal well with views that potentially have a
different set of columns each time they're executed. However, PostgreSQL
has a couple of ways around that: the 'RECORD' data type, and the
'hstore' data type.
I have the feeling that what you want might - kind of - be possible by
combining a crosstab query with the hstore record constructor in
PostgreSQL 9.0 and above. That'll let you produce a view like:
category_id Record_id fields
7821 {Village:adasrpur, SOI:media, Heading:CM dies}
etc. Come to think of it, it'd be possible to do in 8.4 and earlier in
PL/PgSQL, but I'd have to have a play with that. Anyway, check out:
http://www.postgresql.org/docs/current/static/tablefunc.html
http://www.postgresql.org/docs/current/static/hstore.html
in particular the crosstab(text,text) form and the hstore(record)
constructor. Have a play.
It'd be helpful to have your sample data in a useful format like a list
of INSERT statements, a pg_dump, or the output of \COPY rather than just
a plain text list. It'd make testing things easier. I'd like to have a
play, but I can't be bothered making up a sample data set or converting
yours by hand.
BTW, Personally I think you're usually better off using hstore in the
frist place ratherthan EAV, but you're probably already committed to an
EAV model.
--
Craig Ringer
--
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] What's eating my space ?
Eric McKeeth wrote:
I wander what is taking up my space on disk ...
btv=# SELECT pg_size_pretty(pg_database_size('btv_good'));
pg_size_pretty
10 GB
(1 row)
[SELECT total size of all non-system tables]
The sum of biggest tables is not even close to the total db size .
Some index going wild ?
Your sum doesn't contains indexes and toast-tables.
Since he used pg_total_relation_size(), according to the manual
(http://www.postgresql.org/docs/current/interactive/functions-admin.html
) indexes and toast should be
included in the numbers reported for the tables. Unfortunately, I
don't have any insight as to why
pg_database_size() is returning a number roughly 5x larger than the
sum of pg_total_relation_size()
here.
Maybe it's the system tables.
Try running the following query:
SELECT SUM(pg_total_relation_size(C.oid)) AS total_size,
(N.nspname IN ('pg_catalog', 'information_schema')) AS
system_object
FROM pg_class C
JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT OUTER JOIN pg_tablespace T ON (C.reltablespace = T.oid)
WHERE C.relkind 'i'
AND nspname !~ '^pg_toast'
AND COALESCE(T.spcname, 'default') != 'pg_global'
GROUP BY nspname IN ('pg_catalog', 'information_schema');
which will give you a sum of the sizes of all tables and their
appendixes,
grouped by system and non-system tables. I exclude global tables.
On my 8.4 test database I get:
total_size | system_object
+---
376832 | f
5505024 | t
(2 rows)
For
SELECT pg_database_size(current_database())
I get:
pg_database_size
--
5972260
(1 row)
which comes pretty close.
Yours,
Laurenz Albe
--
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] Password issue
Mahmoud wrote: I am trying to create a database by passing arguments to createdb.exe but createdb always asks me about the password although I passed -W 123 to it. How can I override password request? PS This my test for creating the database createdb.exe -U postgres -W 123 -O admin -e test As has been mentioned, -W takes no arguments and prompts you for a password. If you want a password, but don't want the prompt (e.g. because you are writing a script), you could: - Not use -W, then the superuser will have no password initially. - Start the server. - Using trust authentication, connect to a database. - Issue ALTER ROLE ... PASSWORD '...' to set a password. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error compiling sepgsql in PG9.1
I had the following error during compile of sepgsqk contrib: root@postgresql:~/postgresql-9.1beta1/contrib/sepgsql# make sed 's,MODULE_PATHNAME,$libdir/sepgsql,g' sepgsql.sql.in sepgsql.sql gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o hooks.o hooks.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o selinux.o selinux.c selinux.c: In function 'sepgsql_compute_avd': selinux.c:735: warning: implicit declaration of function 'security_deny_unknown' selinux.c:755: error: 'struct av_decision' has no member named 'flags' selinux.c:764: warning: implicit declaration of function 'security_compute_av_flags_raw' selinux.c: In function 'sepgsql_check_perms': selinux.c:917: error: 'struct av_decision' has no member named 'flags' selinux.c:917: error: 'SELINUX_AVD_FLAGS_PERMISSIVE' undeclared (first use in this function) selinux.c:917: error: (Each undeclared identifier is reported only once selinux.c:917: error: for each function it appears in.) make: *** [selinux.o] Error 1 The selinux version is: root@postgresql:~/postgresql-9.1beta1/contrib/sepgsql# dpkg -l | grep seli ii libselinux12.0.55-0ubuntu4 SELinux policy enforcement, run-time librari ii libselinux1-dev2.0.55-0ubuntu4 SELinux policy enforcement, development file -- -- Emanuel Calvo Helpame.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] Convert data into horizontal from vertical form
Hi Adarsh You say you need this to be done dynamically. I assume that by this you're looking for a way to have 1 query produce an increasing number of columns as you increase the number of rows in your table. This really isn't possible and doesn't fit with the model SQL was designed for. The concept of tables is that each table represents a set of items of a single type with a set of known properties (the possible properties are known before the item itself). An item is represented by a row and a property is represented by a column. You are trying to create a query with an unknown set of properties. If the data must be represented as you've shown then you will need to get your front end application to transform the data for you. Regards On 19 May 2011 11:15, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I am not able to insert data into a table in horizontal form. The data is in below form : A show a small set of data :- c_id f_name f_value 2 k1 v1 2 k2 v2 2 k3 v3 2 k4 v4 3 a1 b1 3 a2 b2 3 a3 b3 3 a4 b4 3 a5 b5 1 c1 d1 1 c2 d2 3 a1 e1 3 a2 e2 3 a3 e3 3 a4 e4 3 a5 e5 Now i want to show the above data in horizontal form as per c_id , fore.g if a user enters c_id 3 then output is : c_id a1 a2 a3 a4 a5 3 b1 b2 b3 b4 b5 3 e1 e2 e3 e4 e5 i.e f_name entries became the columns of the table f_value become the rows I research on crosstab function but i don'e think it is useful because we have to give column names in the command. I want to show it dynamically . I try to create a procedure also attach it. A user enters only c_id output is shown fore.g if a user enters c_id 1 then output is c_id c1 c2 1 d1 d2 I show the data in simple way bt there r 1 of rows 100 of c_id's. Please let me know if it is possible or any information is required. Thanks create function user_news_new(text) returns void as $$ declare name text; cat_name alias for $1; begin CREATE TEMPORARY TABLE temptest(category_id INTEGER,category_name text); /* create a temp table to hold all the dynamic schemas*/ for name in select label_name from category_new where category_id = (select category_id from category where category_name=cat_name) loop execute 'alter table temptest add column ' || name || ' text'; end loop; end; $$ language plpgsql; -- 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] Convert data into horizontal from vertical form
2011/5/19 Adarsh Sharma adarsh.sha...@orkash.com: Dear all, I am not able to insert data into a table in horizontal form. The data is in below form : A show a small set of data :- c_id f_name f_value 2 k1 v1 2 k2 v2 2 k3 v3 2 k4 v4 3 a1 b1 3 a2 b2 3 a3 b3 3 a4 b4 3 a5 b5 1 c1 d1 1 c2 d2 3 a1 e1 3 a2 e2 3 a3 e3 3 a4 e4 3 a5 e5 Now i want to show the above data in horizontal form as per c_id , fore.g if a user enters c_id 3 then output is : c_id a1 a2 a3 a4 a5 3 b1 b2 b3 b4 b5 3 e1 e2 e3 e4 e5 i.e f_name entries became the columns of the table f_value become the rows I research on crosstab function but i don'e think it is useful because we have to give column names in the command. I want to show it dynamically . I try to create a procedure also attach it. A user enters only c_id output is shown fore.g if a user enters c_id 1 then output is c_id c1 c2 1 d1 d2 I show the data in simple way bt there r 1 of rows 100 of c_id's. Please let me know if it is possible or any information is required. Something like this? http://wiki.postgresql.org/wiki/Pivot_query -- -- Emanuel Calvo Helpame.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] Convert data into horizontal from vertical form
Emanuel Calvo wrote:
2011/5/19 Adarsh Sharma adarsh.sha...@orkash.com:
Dear all,
I am not able to insert data into a table in horizontal form.
The data is in below form :
A show a small set of data :-
c_id f_name f_value
2 k1 v1
2 k2 v2
2 k3 v3
2 k4 v4
3 a1b1
3 a2b2
3 a3b3
3 a4b4
3 a5b5
1 c1d1
1 c2d2
3 a1e1
3 a2 e2
3 a3e3
3 a4e4
3 a5e5
Now i want to show the above data in horizontal form as per c_id , fore.g if
a user enters c_id 3 then output is :
c_id a1a2 a3 a4 a5
3 b1b2b3 b4 b5
3e1e2 e3e4
e5
i.e f_name entries became the columns of the table f_value become the rows
I research on crosstab function but i don'e think it is useful because we
have to give column names in the command.
I want to show it dynamically . I try to create a procedure also attach
it.
A user enters only c_id output is shown fore.g if a user enters c_id 1
then output is
c_id c1 c2
1 d1 d2
I show the data in simple way bt there r 1 of rows 100 of c_id's.
Please let me know if it is possible or any information is required.
Something like this?
http://wiki.postgresql.org/wiki/Pivot_query
I am able to understand the 2nd procedure as but have some conflicts
with it :
CREATE OR REPLACE FUNCTION pivoty(query text) RETURNS void AS $pivot$
DECLARE
num_cols int;
num_rows int;
table_pivoted text;
columna text;
BEGIN
DROP TABLE IF EXISTS pivoted;
DROP TABLE IF EXISTS pivot_;
EXECUTE 'CREATE TEMP TABLE pivot_ AS ' || query ;
SELECT count(*) INTO num_cols FROM information_schema.COLUMNS WHERE table_name
= 'pivot_';
SELECT count(*) INTO num_rows FROM pivot_;
table_pivoted := 'CREATE TABLE pivoted (';
FOR i IN 1 .. num_rows
LOOP
IF ( i = num_rows )
THEN
SELECT table_pivoted || 'col' || i || ' text ' INTO table_pivoted;
ELSE
SELECT table_pivoted || 'col' || i || ' text ,' INTO table_pivoted;
END IF;
END LOOP;
SELECT table_pivoted || ')' INTO table_pivoted;
EXECUTE table_pivoted;
/**The above if-else condition will create the columns
(col1,col2,col3and so on ) depending upon the rows of original table but I
want the column names depend upon f_name column and category_id input by user*/
/* It may be 10,12,11,15 columns**/
FOR columna IN SELECT column_name::Text FROM information_schema.COLUMNS WHERE
table_name = 'pivot_'
LOOP
EXECUTE 'INSERT INTO pivoted SELECT ((translate(array_agg(' || columna ||
')::text,''{}'',''()'' ))::pivoted).* FROM pivot_';
END LOOP;
/*How to call the procedure * pivoty(query text)
/and how it insert data in new table***/
*END;
$pivot$ LANGUAGE plpgsql;
Thanks
[GENERAL] Unexpected protocol character='j' during authentication..
Saludos al foro. Este error que describe el asunto del post, lo he buscado y no he encontrado una solucion. Describo el ambiente de mi servidor BD. PostgreSql 8.3 Windows 2003 Server Tengo una base de datos a la que se conectan varios vendedores y sincronizan los datos de ventas. Desde hace varios días hemos estado buscando alguna solucion. Re-instalamos postgres, cambiamos la ubicacion de red del servidor, redireccionamos el puerto a otra maquina, entre otras pruebas mas. Cuando cambiamos de router o reseteamos el router, permite la conexion por un periodo de tiempo breve. Luego pasa a dar este molestoso error de conexion: Unexpected protocol character='Ã' during authentication... Por favor, si son tan amables de ayudarme, se los agradeceria mucho. Gracias! Eris J. Gómez ADGSystems, EIRL Gestor de Proyectos Tel. 809.241.7309 ego...@adgsystems.com.do http://www.adgsystems.com.do sg-0.gif
[GENERAL] Views permessions
Hello Guys, There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2' 1. VIEW2 depends on VIEW1 2. VIEW2 and VIEW1 have the exact permissions 3. I can execute SELECT * from VIEW1 ; without problem 4. When I execute SELECT * from VIEW2; I get ERROR: permission denied for relation VIEW1 ** Error ** ERROR: permission denied for relation VIEW1 SQL state: 42501 5. The owner of the views is not me, But I am a super user 6. The Database version is 8.3 What is wrong here and how can I trace the problem, I checked the views permissions many times (i did that manually based on the view def.). Also, Which system catalog maps the permissions and the roles Regards
Re: [GENERAL] Unexpected protocol character='j' during authentication..
Eric, The posts in this forum must be done in english. Entiendo que los posts a este foro deben ser en inglés. May be you already did some of the next: - Test if it works connecting with same credentials from the server itself and other machine. - Raise log/debug level on server and examine the server log file. - Run an sniffer like wireshark somewhere in between the two machines to sniff network traffic, looking for something suspect, don't use ssl connections for this. - Finally may be something of character encoding in the windows client machine, try a fresh linux machine and use psql from command line. A lo mejor ya hiciste algo como lo siguiente: Prueba si funciona conectando con las mismas credenciales desde el mismo servidor u otra máquina conectada directamente al servidor. Incrementa el nivel de debug/log del servidor y examina el log file (archivo postgresql.conf). Ejecuta un sniffer como wireshark en algún lugar entre las dos máquinas para analizar el tráfico de red, para esto no debes usar ssl en la conexión postgres. Finalmente, puede ser algo con la codificación de caracteres en el cliente windows, intenta con un live CD de ubuntu y usa psql desde la línea de comandos. Bye Hans Hans Poo, Welinux S.A. Bombero Ossa #1010, oficina 800, +56-2-3729770, Movil: +56-9-3199305 Santiago, Chile - Mensaje original - De: ego...@adgsystems.com.do Para: pgsql-general@postgresql.org Enviados: Jueves, 19 de Mayo 2011 16:45:01 Asunto: [GENERAL] Unexpected protocol character='j' during authentication.. Saludos al foro. Este error que describe el asunto del post, lo he buscado y no he encontrado una solucion. Describo el ambiente de mi servidor BD. PostgreSql 8.3 Windows 2003 Server Tengo una base de datos a la que se conectan varios vendedores y sincronizan los datos de ventas. Desde hace varios días hemos estado buscando alguna solucion. Re-instalamos postgres, cambiamos la ubicacion de red del servidor, redireccionamos el puerto a otra maquina, entre otras pruebas mas. Cuando cambiamos de router o reseteamos el router, permite la conexion por un periodo de tiempo breve. Luego pasa a dar este molestoso error de conexion: Unexpected protocol character='Ã' during authentication... Por favor, si son tan amables de ayudarme, se los agradeceria mucho. Gracias! Eris J. Gómez ADGSystems, EIRL Gestor de Proyectos Tel. 809.241.7309 ego...@adgsystems.com.do http://www.adgsystems.com.do -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] implementing check-in/check-out of an items table
Hi, I'm trying to implementing the checking in and checking out of items in a table, whereby an item cannot be checked out if it's not checked-in. I've searched for schemas for public libraries where this is a key requirement, but haven't managed to hit the right keywords to get relevant results. Thanks, -- Seb -- 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] Views permessions
salah jubeh s_ju...@yahoo.com writes: There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2' 1. VIEW2 depends on VIEW1 2. VIEW2 and VIEW1 have the exact permissions 3. I can execute SELECT * from VIEW1 ; without problem 4. When I execute SELECT * from VIEW2; I get ERROR: permission denied for relation VIEW1 5. The owner of the views is not me, But I am a super user VIEW2's reference to VIEW1 is checked according to the permissions granted to the owner of VIEW2. Whether the ultimate caller is a superuser doesn't affect this. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Views permessions
I have found the table where views are roles- permissions are stored and I checked it automatically and still permissions are identical i.e. the following query returns 0 rows SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view1' except SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view2' union SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view2' except SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view1'; Do you think there is a bug or something like that... Regards From: Tom Lane t...@sss.pgh.pa.us To: salah jubeh s_ju...@yahoo.com Cc: pgsql pgsql-general@postgresql.org Sent: Friday, May 20, 2011 3:47 PM Subject: Re: [GENERAL] Views permessions salah jubeh s_ju...@yahoo.com writes: There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2' 1. VIEW2 depends on VIEW1 2. VIEW2 and VIEW1 have the exact permissions 3. I can execute SELECT * from VIEW1 ; without problem 4. When I execute SELECT * from VIEW2; I get ERROR: permission denied for relation VIEW1 5. The owner of the views is not me, But I am a super user VIEW2's reference to VIEW1 is checked according to the permissions granted to the owner of VIEW2. Whether the ultimate caller is a superuser doesn't affect this. regards, tom lane
Fw: [GENERAL] Views permessions
- Forwarded Message - From: salah jubeh s_ju...@yahoo.com To: Tom Lane t...@sss.pgh.pa.us Cc: pgsql pgsql-general@postgresql.org Sent: Friday, May 20, 2011 3:54 PM Subject: Re: [GENERAL] Views permessions I have found the table where views are roles- permissions are stored and I checked it automatically and still permissions are identical i.e. the following query returns 0 rows SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view1' except SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view2' union SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view2' except SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy FROM information_schema.role_table_grants WHERE table_name = 'view1'; Do you think there is a bug or something like that... Regards From: Tom Lane t...@sss.pgh.pa.us To: salah jubeh s_ju...@yahoo.com Cc: pgsql pgsql-general@postgresql.org Sent: Friday, May 20, 2011 3:47 PM Subject: Re: [GENERAL] Views permessions salah jubeh s_ju...@yahoo.com writes: There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2' 1. VIEW2 depends on VIEW1 2. VIEW2 and VIEW1 have the exact permissions 3. I can execute SELECT * from VIEW1 ; without problem 4. When I execute SELECT * from VIEW2; I get ERROR: permission denied for relation VIEW1 5. The owner of the views is not me, But I am a super user VIEW2's reference to VIEW1 is checked according to the permissions granted to the owner of VIEW2. Whether the ultimate caller is a superuser doesn't affect this. regards, tom lane
Re: [GENERAL] Connecting to Postgres using Windows 7
- Original Message - From: Geoffrey Becker I'm relatively new to postgres. I've got a Visual Basic (VB) application that i would like to connect to a Postgres database using ODBC . Both the VB application and postgres are on my laptop and both work beautifully independent of each other. Trouble is, I have a windows 7 64bit OS and therefore I have been unable to get VB to connect to the database. My code is as follows: Public Class Form1 Dim cCon As ADODB.Connection Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click On Error GoTo EH cCon.ConnectionString = DSN=MyDatabase;uid=MyUserID;pwd=MyPassword Is this your actual connection string? If your using a DSN does it specify the Server Name and Port where PostgreSQL is listening? See: http://www.connectionstrings.com/postgre-sql cCon.Open() MsgBox(O.K., vbInformation, Connection Message) Exit Sub EH: MsgBox(Err.Description, vbCritical, Error Message) End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cCon = New ADODB.Connection End Sub End Class When I try to connect, all I get is a Server does not exist or access is denied error. I've tried configuring ODBC using odbcad32.exe as it seems that is necessary on a 64 bit OS, but I'm not sure if that even the right way to go. You may need to install the psqlODBC 64 bit driver if you trying to connect from a 64 bit machine. Any help i could get would be much appreciated. Thanks, Geoff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
On 5/20/2011 8:41 AM, Seb wrote: Hi, I'm trying to implementing the checking in and checking out of items in a table, whereby an item cannot be checked out if it's not checked-in. I've searched for schemas for public libraries where this is a key requirement, but haven't managed to hit the right keywords to get relevant results. Thanks, Use a loans table with unique partial index to ensure that only one unreturned loan per item can exist at a time. CREATE TABLE items( item_id SERIAL PRIMARY KEY, ... ); CREATE TABLE loans( loan_id SERIAL, item_id integer NOT NULL REFERENCES items, start_time timestamptz NOT NULL, end_time timestamptz ... ); CREATE UNIQUE INDEX ON loans(item_id) WHERE end_time IS NULL; -- Jack Christensen ja...@hylesanderson.edu -- 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] implementing check-in/check-out of an items table
On Fri, 20 May 2011 09:48:45 -0500, Jack Christensen ja...@hylesanderson.edu wrote: On 5/20/2011 8:41 AM, Seb wrote: Hi, I'm trying to implementing the checking in and checking out of items in a table, whereby an item cannot be checked out if it's not checked-in. I've searched for schemas for public libraries where this is a key requirement, but haven't managed to hit the right keywords to get relevant results. Thanks, Use a loans table with unique partial index to ensure that only one unreturned loan per item can exist at a time. [...] Thanks, this certainly avoids loaning an item before it's returned, but it doesn't protect against having loans that overlap in time. For example, an item can have a start_time that is between start_time and end_time of a previous loan for that same item. My first thought was to have some CHECK constraint with a query, but this doesn't seem to be supported by postgresql. -- Seb -- 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] implementing check-in/check-out of an items table
On Fri, May 20, 2011 at 08:41:06AM -0500, Seb wrote: Hi, I'm trying to implementing the checking in and checking out of items in a table, whereby an item cannot be checked out if it's not checked-in. I've searched for schemas for public libraries where this is a key requirement, but haven't managed to hit the right keywords to get relevant results. It could be that your analogy there is a little too simple (library management systems' circulation modules are often incredibly complicated, because of the different classes of restriction on circulation). Nevertheless, there is a system built atop Postgres or SQLite: http://biblioteq.sourceforge.net/index.html. I'm unable to find the license, though the web page says it's open source. 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] implementing check-in/check-out of an items table
On 5/20/2011 10:15 AM, Seb wrote: On Fri, 20 May 2011 09:48:45 -0500, Jack Christensenja...@hylesanderson.edu wrote: Use a loans table with unique partial index to ensure that only one unreturned loan per item can exist at a time. [...] Thanks, this certainly avoids loaning an item before it's returned, but it doesn't protect against having loans that overlap in time. For example, an item can have a start_time that is between start_time and end_time of a previous loan for that same item. My first thought was to have some CHECK constraint with a query, but this doesn't seem to be supported by postgresql. In a similar project I worked on start time for a loan was always the current time so overlaps weren't an issue. I don't have any firsthand experience with them, but it sounds like what you want are exclusion constraints. http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ -- Jack Christensen ja...@hylesanderson.edu -- 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] implementing check-in/check-out of an items table
On Fri, 20 May 2011 12:08:34 -0400, Andrew Sullivan a...@crankycanuck.ca wrote: [...] It could be that your analogy there is a little too simple (library management systems' circulation modules are often incredibly complicated, because of the different classes of restriction on circulation). Nevertheless, there is a system built atop Postgres or SQLite: http://biblioteq.sourceforge.net/index.html. I'm unable to find the license, though the web page says it's open source. Thanks, they do provide the sql schema in postgresql, so this is very helpful. Thanks, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error compiling sepgsql in PG9.1
On Fri, 2011-05-20 at 11:02 +0200, Emanuel Calvo wrote: I had the following error during compile of sepgsqk contrib: Apparently we need to specify the minimum SELinux version that we can compile sepgsql against. It builds fine on my Fedora 14 box, where I have libselinux 2.0.96. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] implementing check-in/check-out of an items table
On Fri, 20 May 2011 11:37:36 -0500, Jack Christensen ja...@hylesanderson.edu wrote: [...] In a similar project I worked on start time for a loan was always the current time so overlaps weren't an issue. I don't have any firsthand experience with them, but it sounds like what you want are exclusion constraints. http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ This lead to a more general solution presented nicely by Jeff Davis: http://pgfoundry.org/projects/temporal http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL Path in psql
I come from an Oracle background and I am fairly new to Postgres. Oracle's command line utility (SQL*Plus) uses an environment variable called SQLPATH to search the given directories for SQL scripts that the user calls to execute using a method similar to the \i meta-command in psql. This allows users to put SQL scripts in various directories and execute them no matter which directory is there current working directory in psql. Is there a similar environment variable that can be used with psql, or another way to mimic this behavior? Thanks, Bobby
Re: [GENERAL] Error compiling sepgsql in PG9.1
As documentation said, it needs libselinux 2.0.93 or higher. This version supports selabel_lookup(3) for database object classes. Thanks, -- NEC Europe Ltd, SAP Global Competence Center KaiGai Kohei kohei.kai...@emea.nec.com -Original Message- From: Devrim GÜNDÜZ [mailto:dev...@gunduz.org] Sent: 20. Mai 2011 19:05 To: Emanuel Calvo Cc: postgresql Forums; KaiGai Kohei Subject: Re: [GENERAL] Error compiling sepgsql in PG9.1 On Fri, 2011-05-20 at 11:02 +0200, Emanuel Calvo wrote: I had the following error during compile of sepgsqk contrib: Apparently we need to specify the minimum SELinux version that we can compile sepgsql against. It builds fine on my Fedora 14 box, where I have libselinux 2.0.96. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to Install - unable to write inside TEMP environment variable path
When I install the 9.0.x version on my Window Visita Laptop, I get the error. The "Windows Scripting Host" is up. So it is not the topic of this article:http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html How to resolve this problem? Thanks. - w
Fwd: [GENERAL] Unable to Install - unable to write inside TEMP environment variable path
Forwarded message From: Wei wei...@lycos.com Date: May 20, 2011 Subject: [GENERAL] Unable to Install - unable to write inside TEMP environment variable path To: pgsql-general@postgresql.org When I install the 9.0.x version on my Window Visita Laptop, I get the error. The Windows Scripting Host is up. So it is not the topic of this article: http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html How to resolve this problem? Thanks. - w -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
