Good evening!
Why does PostgreSQL 9.5.4 print the error:
LOG: execute : SELECT out_uid AS uid, out_fcm AS fcm,
out_apns AS apns, out_sns AS sns, out_note AS note FROM
words_resign_game($1::int, $2::int)
DETAIL: parameters: $1 = '2', $2 = '1'
ERROR: type " " does not exist at character 1
Good evening everyone,
I apologize for the delay in replying and that you had to "reverse
engineer" my question.
This turned out indeed to be a special char problem.
On MBA with macOS Sierra 10.12.5 I am using Postgres 9.5.4.1 of
postgresapp.com.
At the psql prompt I had copy-pasted:
words=> S
Good evening,
with PostgreSQL 9.5 I have extended a larger custom function, which has
worked well before and my problem is that the error message returned by the
database is rather cryptic:
words=> select * from words_skip_game(1, 1);
ERROR: query returned no rows
CONTEXT: PL/pgSQL function wor
On Mon, Jun 26, 2017 at 8:39 PM, Moreno Andreo
wrote:
> Il 26/06/2017 20:21, Alexander Farber ha scritto:
>
>>
>> RETURNING
>> player1,
>> score2,
>>
Hi again,
On Mon, Jun 26, 2017 at 8:21 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:
>
> words=> select * from words_skip_game(1, 1);
> ERROR: query returned no rows
> CONTEXT: PL/pgSQL function words_skip_game(integer,integer) line 85 at
> SQL stateme
In my case _opponent was NULL and there are no records in words_users with
PK uid being NULL... so that was the reason.
Thank you
Thank you Adrian, with \sf+ words_skip_game(integer, integer) the line 85
was correct
Good afternoon,
in PostgreSQL 9.5 with pgbouncer (having "pool_mode = session" and
"server_reset_query = DISCARD ALL") 2-player games are stored in the
following table:
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
finished timestamptz,
I have tried:
FOR _gid, _loser, _winner IN
UPDATE words_games
SET finished = CURRENT_TIMESTAMP
WHERE finished IS NULL
AND played1 IS NOT NULL
AND played2 IS NOT NULL
AND (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours'
Hi David,
On Mon, Jul 10, 2017 at 10:02 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>>
>> However there is a problem: I can not use a "single-instance
Good afternoon,
in 9.5.7 I have the following 2 tables -
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
visited timestamptz NOT NULL,
ip inetNOT NULL,
fcm text,
apnstext,
sns t
Thank you, Tom!
Should I have the CHECK in the new table written out again as in -
On Sat, Jul 29, 2017 at 3:41 PM, Tom Lane wrote:
>
> You have to use the separate-constraint FK syntax:
>
> CREATE TABLE words_payments (
> sid textNOT NULL,
> social integer NOT N
Good evening,
I have a custom SQL function in PostgreSQL 9.5.7 which adds a "log entry"
to the table words_payments and then updates "vip_until" column in another
table:
CREATE OR REPLACE FUNCTION words_buy_vip(
in_sid text,
in_social integer,
in_tid text,
in_item
Hello, I have followed David's suggestion (thank you!) -
On Wed, Aug 2, 2017 at 6:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> However if the user
Good evening,
with PostgreSQL 9.6.3 and JDBC 42.1.1.jre7 types can be casted when calling
a stored function:
final String sql = "SELECT words_buy_vip(?::text, ?::int, ?::text,
?::text, ?::float, ?::inet)";
try (Connection db = DriverManager.getConnection(DATABASE_URL,
DATABASE_US
Hi Steve,
On Fri, Aug 18, 2017 at 7:50 PM, Steve Clark
wrote:
>
> I loaded 9.5 on CentOS 7 but by default every thing wants to use the
> default
> 9.2 version that comes with CentOS 7.
>
> Is there a simple way to fix this so the 9.5 version of tools and
> libraries are used.
>
>
to use PostgreS
Oops, one correction - instead of
systemctl initdb postgresql-9.6
please use
/usr/pgsql-9.6/bin/postgresql96-setup initdb
as explained at https://wiki.postgresql.org/wiki/YUM_Installation
Regards
Alex
Good evening,
In a word game I store all player moves in the table:
CREATE TYPE words_action AS ENUM (
'play',
'skip',
'swap',
'resign',
'ban',
'expire'
);
CREATE TABLE words_moves (
mid BIGSERIAL PRIMARY KEY,
action words_acti
Hello, I appreciate your comments, thank you
Hello,
in PostgreSQL 9.5 I have a table with 67000 records:
# \d words_nouns
Table "public.words_nouns"
Column | Type | Modifiers
-+--+---
word| text | not null
hashed | text |
Sorry, I probably had to call:
# EXPLAIN SELECT ARRAY_AGG(hashed)
FROM words_nouns
WHERE EXTRACT(EPOCH FROM added) > 0
UNION
SELECT ARRAY_AGG(hashed)
FROM words_verbs
WHERE EXTRACT(EPOCH FROM adde
# EXPLAIN SELECT ARRAY_AGG(hashed)
FROM words_nouns
WHERE added > to_timestamp(0)
UNION
SELECT ARRAY_AGG(hashed)
FROM words_verbs
WHERE added > to_timestamp(0)
Hello,
on CentOS 6.4 I'm moving from 8.4 to 9.3, but can not find the file
/var/lib/pgsql/9.3/data/global/pg_auth which I need for the pg_bouncer.
Does anybody please know where to find that file?
My packages:
postgresql93-9.3.1-1PGDG.rhel6.x86_64
pgdg-centos93-9.3-1.noarch
postgresql93-libs-9.
d file, so this setting can be pointed directly to
one of those backend files.
What is the 3rd column of the file used for?
(the 1st being username and
the 2nd being md5 hashed password)
Regards
Alex
On Wed, Nov 13, 2013 at 12:43 PM, Devrim GÜNDÜZ wrote:
> On Wed, 2013-11-13 at 12:28 +0100, Alex
Hello,
do these changes please look okay for a PostgreSQL 9.3 running on CentOS
6.4 server with 32 GB RAM (with Drupal 7 and few custom PHP scripts)
postgresql.conf:
shared_buffers = 4096MB
work_mem = 32MB
checkpoint_segments = 32
log_min_duration_statement = 1
sysctl.conf:
Hello, my problem with
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
is that it never mentions larger amounts of RAM,
so while reading it I always wonder how up-to-date it is...
And pgtune is 4 years old...
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 7680MB
max_connections = 80
Is it really okay? Isn't 22GB too high?
And how does it know that max_connections =80 is enough in my case? (I use
pgbouncer).
Regards
Alex
On Thu, Nov 14, 2013 at 4:04 PM, Alexander Farber <
alexa
Hello,
I have installed CentOS from a CentOS-5.6-x86_64-bin-DVD-1of2.iso
and then installed PGDG because I want to have PostgreSQL 8.4.7:
# rpm -Uvh
http://www.pgrpms.org/8.4/redhat/rhel-5-x86_64/pgdg-centos-8.4-2.noarch.rpm
# cat /etc/yum.repos.d/pgdg-84-centos.repo
[pgdg84]
name=PostgreSQL 8
Thank you Tom, "yum install postgresql84" has worked for CentOS 5.6/64 bit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello fellow PostgreSQL-users,
I run a Drupal 7 (+Facebook app) website
with a multiplayer flash game and use
postgresql-server-8.4.8-1PGDG.rhel5 +
CentOS 5.6 64 bit on a Quad-Core/4GB machine.
I generally like using PostgreSQL eventhough
I'm not an experienced DB-user, but in the recent
weeks it
Thank you for your replies,
I've reverted httpd.conf to
StartServers 8
MinSpareServers5
MaxSpareServers 20
ServerLimit 256
MaxClients 256
and have changed postgresql.conf to:
shared_buffers = 512MB
# for Apache + my game daemon + cron jobs
max_connections
# sysctl kernel.shmmax
kernel.shmmax = 68719476736
# sysctl kernel.shmall
kernel.shmall = 4294967296
On Wed, May 25, 2011 at 9:54 PM, Alexander Farber
wrote:
> shared_buffers = 512MB
>
> Do you think I need to reconfigure CentOS 5.6
> for the bigger shared memory too or
> w
Thank you, I'll try your suggestions.
I'm just slow in doing so, because it's just a
(sometimes pretty time consuming) hobby-project.
I'm missing knowledge on how to monitor my DB status,
i.e. how to check some of the things you've asked.
Also I wonder, how's shared memory used by PostgreSQL.
I'
I've switched duration and SQL 'all' logging on,
but I have hard time to identify which SQL statement
has had which duration.
For example which SQL statement please has
the duration of 13 seconds (13025.016 ms) below?
LOG: statement: SELECT 1 AS expression
FROM
drupal_sessions s
But when I try to look at that wrong index it seems to be ok?
# psql
psql (8.4.8)
Type "help" for help.
pref=> \d pref_match
Table "public.pref_match"
Column | Type |Modifiers
---+---+--
Actually I have 1 db user accessing 1 db name
(through PHP scripts and 1 game daemon in Perl)
On Thu, May 26, 2011 at 6:23 PM, Scott Marlowe wrote:
> You need to log more stuff. Look at the log_line_prefix setting, and
> add things like pid, username, database name, etc.
>
--
Sent via pgsq
Hello,
does anybody know of a good source for a apr-util-pgsql
rpm package for CentOS 5.6 / 64 bit and even more I'm curious,
why isn't it included but the apr-util-mysql is included...
Is apr-util-pgsql maybe part of some bigger package already?
I'm using:
# rpm -qa|grep post
compat-postgresql-
Hello,
I'm still suffering with my Drupal 7.2 site and
PostgreSQL 8.4.8 every evening, for example
right now. I have tried different combinations
for /etc/pgbouncer.ini - for example now I have:
[databases]
pref = host=/tmp user=pref password=XXX dbname=pref
[pgbouncer]
logfile = /var/log/pgboun
On 6/16/11, Cédric Villemain wrote:
> 2011/6/16 Alexander Farber :
>> I'm still suffering with my Drupal 7.2 site and
>> PostgreSQL 8.4.8 every evening, for example
>> right now. I have tried different combinations
>> for /etc/pgbouncer.ini - for example now I ha
On 6/16/11, Scott Marlowe wrote:
> What do vmstat 1 and iostat -xd 1 (or equivalent for your OS) say? So
> here's a healthy not working too hard machine:
>
> procs ---memory-- ---swap-- -io -system--
> cpu
> r b swpd free buff cache si sobibo
Hello everyone,
after the suggestion from this mailing list,
I have installed pgbouncer at my
CentOS 5.6 / 64 bit server and
activated its transaction mode:
[databases]
pref = host=/tmp user=pref password=XXX dbname=pref
[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/p
Hello Cedric and others,
On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain
wrote:
> 2011/6/19 Alexander Farber :
>> [pgbouncer]
>> logfile = /var/log/pgbouncer.log
>> pidfile = /var/run/pgbouncer/pgbouncer.pid
>> listen_port = 6432
>> unix_socket_dir = /tmp
>&
I've added
$db->beginTransaction();
$db->commit();
around _all_ statements, but now get:
SQLSTATE[25P02]: In failed sql transaction:
7 ERROR: current transaction is aborted,
commands ignored until end of transaction block
quite often. I don't understand why
would transaction
Hell Marko and others,
On Mon, Jun 20, 2011 at 4:08 PM, Marko Kreen wrote:
>> Maybe I should try session mode of pgbouncer
>> again, now that I've got rid of the persistent
>> PHP connections?
>
> You could, but try to turn off prepared
> statements in PDO first.
isn't having prepared statements
Nope, pool_mode = session kills my site...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
PDO::ATTR_EMULATE_PREPARES => true
kills my server too...
On Mon, Jun 20, 2011 at 7:34 PM, Alexander Farber
wrote:
> Nope, pool_mode = session kills my site...
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscript
Sorry for the late reply - but I still haven't found a solution,
for example I have a PHP script with 5 consecutive SELECT
statements (source code + problem described again under:
http://stackoverflow.com/questions/6458246/php-and-pgbouncer-in-transaction-mode-current-transaction-is-aborted
) and
Hello,
at my website users can rate each other:
# select id, nice, last_rated from pref_rep where nice=true
order by last_rated desc limit 7;
id | nice | last_rated
+--+
OK152565298368 | t| 2011-07
Hello,
I will do 2 queries -
one for female users (to find "the miss of last month)
and one for males (the "mister of last month").
Here I can fetch all females rated nicely in June:
# select r.id, nice, r.last_rated
from pref_rep r, pref_users u
where r.nice=true and
to_char(current_timestamp -
Do you think this query is good?
(or is it allocating loads
of strings for the month comparisons?)
# select r.id, count(r.id)
from pref_rep r, pref_users u
where r.nice=true and
to_char(current_timestamp - interval '1 month', 'IYYY-MM') =
to_char(r.last_rated, 'IYYY-MM') and
u.female=true and
r.id
This seems to work, but I wonder
if my query for "the miss of the last month"
could be improved
# select r.id, count(r.id), u.first_name, u.avatar, u.city
from pref_rep r, pref_users u
where r.nice=true and
to_char(current_timestamp - interval '1 month', 'IYYY-MM') =
to_char(r.last_rated, 'IYYY-MM
Hello,
I'm running a CentOS 5.6 / 64 bit Linux
with PostgreSQL 8.4.8 and Drupal 7.4
on a 4GB quad-CPU machine.
In Autumn I can change my hoster
and for EUR 100 can either take
1) 2 machines with i7-920 Quad-Core
8 GB RAM, 2 x 750 GB SATA-II HDD
(Software-RAID 1)
or
2) 1 machine i7-980X H
Hi,
On Sat, Jul 9, 2011 at 6:53 PM, Scott Marlowe wrote:
> On Sat, Jul 9, 2011 at 10:19 AM, Alexander Farber
> wrote:
>>
>> 1) 2 machines with i7-920 Quad-Core
>> 8 GB RAM, 2 x 750 GB SATA-II HDD
>> (Software-RAID 1)
>>
>> 2) 1 machine i7-980X Hexa
Hello,
I'm using CentOS 6.0 Linux 64 bit with the stock packages:
# rpm -qa|grep php
php-cli-5.3.2-6.el6_0.1.x86_64
php-5.3.2-6.el6_0.1.x86_64
php-xml-5.3.2-6.el6_0.1.x86_64
php-pgsql-5.3.2-6.el6_0.1.x86_64
php-pear-1.9.0-2.el6.noarch
php-pdo-5.3.2-6.el6_0.1.x86_64
php-common-5.3.2-6.el6_0.1.x86_
Hello,
I run a LAPP server (PostgreSQL 8.4 @ CentOS 5.7 / 64 bit;
only 4 GB RAM) with the following config:
postgresql.conf (unix socket only and - ):
max_connections = 50
shared_buffers = 1024MB # min 128kB
pgbouncer.ini:
[databases]
pref = host=/tmp user=XXX pas
Hello,
I use PostgreSQL 8.4 under CentOS 5.7:
# rpm -qa | grep post
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-8.4.9-1PGDG.rhel5
postgresql-server-8.4.9-1PGDG.rhel5
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-libs-8.4.9-1PGDG.rhel5
postgresql-devel-8.4.9-1PGDG.rhel5
And perform nightly
Thank you Michael, but no -
On Wed, Oct 5, 2011 at 5:24 PM, Michael Glaesemann wrote:
>> psql:pref-2011-10-05-a.sql:339: ERROR: function "pref_update_match"
>> already exists with same argument types
>> ALTER FUNCTION
>
> Likely someone mistakenly added the functions to template1 of the machine
Hello,
I use PostgreSQL 8.4.7 on CentOS 6.0 / 64 bit machine
with Quad-Core AMD Opteron(tm) Processor 2352 and
16 GB RAM and use it for 1 PHP script - which selects
and displays data in jQuery DataTables (i.e. an
HTML-table which can be viewed page by page).
I select records from 1 view which uni
Thanks for your comments,
the problem has disappeared on the 2nd restore,
but I'll keep you suggestions in mind!
On Thu, Oct 6, 2011 at 10:00 AM, Albe Laurenz wrote:
>> template1=# \df
>> List of functions
>> Schema | Name | Result data type | Argument data types | Type
>>
Hello Bill and others,
On Thu, Oct 13, 2011 at 4:09 PM, Bill Moran wrote:
> In response to Alexander Farber :
>> I use PostgreSQL 8.4.7 on CentOS 6.0 / 64 bit machine
>> with Quad-Core AMD Opteron(tm) Processor 2352 and
>> 16 GB RAM and use it for 1 PHP script - which se
I've also tried opening cursor:
quincy=> open ref for select to_char(qdatetime, '-MM-DD') as
QDATETIME,ID,NAME,CATEGORY,APPSVERSION,OSVERSION,DETAILS,DEVINFO from
quincyview where qdatetime <= now() order by QDATETIME desc ;
ERROR: syntax error at or near "open"
LINE 1: open ref for select to
Thank you -
On Fri, Oct 14, 2011 at 11:30 AM, Pavel Stehule wrote:
> you should to use a DECLARE statement
> http://www.postgresql.org/docs/9.1/interactive/sql-declare.html
> and fetch statement
> http://www.postgresql.org/docs/9.1/interactive/sql-fetch.html
I've managed to create a cursor
and c
Hi Alban and others -
On Fri, Oct 14, 2011 at 1:34 PM, Alban Hertroys wrote:
> Anyway, I think you get the sequential scans because the UNION requires to
> sort all the data from both tables to guarantee that the results are unique
> (hence that long Sort Key at the 7th line of explain output).
Hello,
I'm trying to create the following function which gives me
a runtime error, because it obviously doesn't return a mere
integer but several rows and columns (result of a join):
# create or replace function pref_daily_misere() returns setof integer as $BODY$
begin
create
Hello fello PostgreSQL users,
with PHP 5.3.3 and PostgreSQL 8.4.11
(and a pgbouncer, but I've tried without it too)
I'm trying to execute several SQL queries
with 2 temp tables (listed below) and then use
the result of a final join to construct a JSON array.
Unfortunately my script using prepare/
Thank you Misa, the without-temp-tables query has worked flawlessly.
On Wed, Jun 13, 2012 at 5:01 PM, Misa Simic wrote:
> I think you can have all in one query, without temp tables:
>
> SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.
> HH24:MI') as day,
> c.bid, c.trix, c
Hello,
I run CentOS 6.3 server with 16 GB RAM and:
postgresql-8.4.12-1.el6_2.x86_64
pgbouncer-1.3.4-1.rhel6.x86_64
The modified params in postgresql.conf are:
max_connections = 100
shared_buffers = 4096MB
and the pgbouncer runs with:
pool_mode = session
server_reset_query
Hello, thank you for your replies and sorry for the delay in my replying -
On Thu, Aug 30, 2012 at 4:45 PM, Scott Marlowe wrote:
> On Thu, Aug 30, 2012 at 8:42 AM, Scott Marlowe
> wrote:
>> users, and currently work_mem is set to 1M (the default.) If you
>> increase that to 16M, that'd be max
On Tue, Sep 4, 2012 at 10:59 AM, Alexander Farber
wrote:
> I'll try changing work_mem to 2MB first - once I upgrade the RAM.
And then I'll increase it up to 16MB every day as Scott proposed.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make ch
Hello Scott and others,
On Sat, Sep 29, 2012 at 9:38 PM, Scott Marlowe wrote:
> On Sat, Sep 29, 2012 at 11:27 AM, Alexander Farber
> wrote:
>> I've finally doubled up RAM to 32 GB for my Quad core
>> CentOS 6.3 server and have changed postgresql.conf to
>>
On Sun, Sep 30, 2012 at 2:36 AM, Scott Marlowe wrote:
>> Whoa aren't you running pg bouncer? If so then leave pg alone, adjust
>> pg bouncer. Revert that db side change, examine pgbouncer config etc.
>
>
> apache/php -> (500 persistent conns, cheap) -> pgbouncer -> (20
> persistent pgsql conns,
Thank you, I've ended up with:
# select
u.uid,
/* u.pass, */
f.filename as avatar,
(g.field_gender_value='Female') as female,
c.field_city_value as city
from
drupal_users u LEFT OUTER JOIN drupal_file_managed f on
(u.picture=f.fid),
drupal_fi
Hello,
why aren't double quotes accepted below?
db1=# alter user user1 password "pass1";
ERROR: syntax error at or near ""pass1""
LINE 1: alter user user1 password "pass1";
^
db1=# alter user user1 password 'pass1';
ALTER ROLE
Is there a thumb rule to know when
And identifiers means column names (eventually containing whitespace)?
Thank you
Alex
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello,
I'm using phpBB 3.0.7-PL1 with postgresql-server-8.1.21-1.el5_5.1 with
CentOS Linux 5.5, both under 32-bit (dev. VM) and 64-bit (prod. server)
One of the phpBB sub-forums grows quickly every day and I have problems
cleaning old messages there, because its phpBB's admin-script bails out wit
How do you copy a column from table please?
I'm trying with PostgreSQL 8.1.21/CentOS 5.5:
# psql -a -f clean-forum.sql
start transaction;
START TRANSACTION
create temp table old_topics (topic_id integer) on commit delete rows;
CREATE TABLE
create temp table old_posts (post_id integer) on commit de
Ouch! I've loaded my backup while being connected
to the wrong database - to the dafeult "postgres" database.
Is there a way to restore it? I currently have:
postgres=# \l
List of databases
Name| Owner | Encoding
---+--+--
postgres | postgres | UTF8
ph
duction server).
Regards
Alex
On Fri, Oct 1, 2010 at 4:59 PM, Gurjeet Singh wrote:
> On Fri, Oct 1, 2010 at 10:52 AM, Alexander Farber
> wrote:
>>
>> Thank you, I've created the following script which seems to work ok.
>>
>> I just hope, that it won't be
I wish I could see those hanging queries, what SQL do they try to execute:
pref=> select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |current_query
| query_start | backend_start | client_addr |
client_port
---+-+-+
Hello again,
ouch, I have these postmaster's spinning up again -
even though I've disabled the cronjob and rebooted:
top - 11:42:31 up 1:22, 2 users, load average: 9.15, 7.59, 5.03
Tasks: 135 total, 10 running, 125 sleeping, 0 stopped, 0 zombie
Cpu0 : 31.7%us, 0.5%sy, 0.0%ni, 67.4%id,
I've upgraded my CentOS 5.5 VM to:
postgresql-libs-8.4.4-1PGDG.rhel5
postgresql-devel-8.4.4-1PGDG.rhel5
postgresql-8.4.4-1PGDG.rhel5
postgresql-contrib-8.4.4-1PGDG.rhel5
postgresql-docs-8.4.4-1PGDG.rhel5
postgresql-server-8.4.4-1PGDG.rhel5
postgresql-plperl-8.4.4-1PGDG.rhel5
and then have loaded
Hello Postgres users,
I have a Linux website with phpBB serving a small Facebook game:
# uname -a
Linux X 2.6.18-194.17.1.el5 #1 SMP Wed Sep 29 12:50:31 EDT 2010
x86_64 x86_64 x86_64 GNU/Linux
# cat /etc/redhat-release
CentOS release 5.5 (Final)
# rpm -qa | grep -i postgres
postgresql-devel-8
Thank you, I've enabled logging,
but couldn't see anything suspicious there.
Also the 60%-120% CPU-eating postmaster
processes are now suddenly gone.
I suspect this is some bug in the
postgresql-server-8.1.21-1.el5_5.1 delivered
with CenOS 5.5/64 bit, because I don't
have many simultaneous users
Hello,
I have multiplayer card game in Flash/Perl/C and would
like to add weekly tournaments/player ratings to it.
This means I have to add a table which holds:
player id, weekly score (which I update after each round)
and the week number.
Does anybody has an advice how to save the week number?
Hello,
I see the errors
ERROR: value too long for type character varying(32)
CONTEXT: SQL statement "update pref_users set first_name = $1 ,
last_name = $2 , female = $3 , avatar = $4 , city = $5 , last_ip =
$6 , login = now() where id = $7 "
PL/pgSQL function "pref_update_users"
Hello,
sadly I have to ban few users from my game web
site daily and so I'm trying to write a procedure for
first copying their id, name, city, IP into a pref_ban table
and then erasing their comments and statistics:
create or replace function pref_delete_user(_id varchar,
_re
Hello,
I have a table in PostgreSQL 8.4.9 storing
user comments (in the "about" field):
# \d pref_rep
Table "public.pref_rep"
Column |Type | Modifiers
+-+---
id | character varying(32)
Hello,
I'm trying to save results of card game with 3 players into a table.
It is bad enough, that I had to introduce
3 columns for user ids: id0, id1, id2 and
3 columns for their scores: money0, money1, money2 -
create table pref_results (
id0 varchar(32) references pref
Hello again,
thank you for your replies. If I create a separate table for games:
create table pref_users (
uid varchar(32) primary key,
first_name varchar(64),
female boolean,
avatar varchar(128)
}
create table pref_games {
Hello again,
still I can't figure out how to perform a join
to fetch all games where a player has participated -
I have a table containing all games played:
# select * from pref_games limit 5;
gid | rounds | finished
-++
1 | 10 | 2011-10-26
Thank you Michal and others -
On Wed, Oct 26, 2011 at 11:11 PM, Michael Glaesemann
wrote:
> Get games for a particular user:
>
> SELECT g.gid, g.rounds, g.finished
> FROM pref_games g
> JOIN pref_scores u USING (gid)
> WHERE u.id = :id;
>
> Now, add the participants for those games
>
> SELECT
The PostgreSQL docs are unfortunately scarce on JOINs
http://www.postgresql.org/docs/8.4/static/tutorial-join.html
I've never seen a JOIN producing several rows instead
of columns before Michael suggested it in this thread
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
Hello,
in PostgreSQL 8.4.9 I'm able to
select all games and his partners by a player id:
# select * from pref_scores s1
join pref_scores s2 using(gid)
join pref_games g using(gid)
where s1.id='OK531282114947';
gid | id | money | quit | id | money |
quit | rounds |
First half is
delete from pref_scores where gid in
(select gid from pref_scores where id=_id);
but how to clean pref_games?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/
Nevermind, sorry - I've figured it out myself (for a change)
delete from pref_scores where id<>_id and gid in
(select gid from pref_scores where id=_id);
delete from pref_games where gid in
(select gid from pref_scores where
Thank you, David -
On Fri, Oct 28, 2011 at 7:00 PM, David Johnston wrote:
> The easiest way is to create FOREIGN KEY relationships between the various
> tables and allow "ON DELETE CASCADE" to do the work.
>
> Otherwise you need to DELETE with an appropriate WHERE clause (and
> sub-selects) or yo
For now I'm trying to finish my 1st approach
(without "on delete cascade") and the following
strangely fails with
ERROR: syntax error at "temp"
DETAIL: Expected record variable, row variable, or list of scalar
variables following INTO.
CONTEXT: compilation of PL/pgSQL function "pref_delete_user
On Fri, Oct 28, 2011 at 7:49 PM, Alban Hertroys wrote:
> SELECT INTO in PL/pgSQL isn't the same command as SELECT INTO in SQL.
>
> Check the documentation for the two ;)
>
> Alban Hertroys
Thanks and I'm not surprised about this news...
--
Sent via pgsql-general mailing list (pgsql-general@post
This seems to work (I'm not sure if ON COMMIT DROP
is needed or not - I'm using non-persistent PHP 5.3 script
with pgbouncer pool_mode=session and PostgreSQL 8.4.9):
create or replace function pref_delete_user(_id varchar,
_reason varchar) returns void as $BODY$
201 - 300 of 345 matches
Mail list logo
