Hot answers tagged postgresql-9.1
10
In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables.
I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad.
Tune the storage parameters of your tables and indexes
Besides adding several indexes to the ...
9
Generally, col IS NULL is a possible candidate for a (default) b-tree index search.
I quote the manual here:
Also, an IS NULL or IS NOT NULL condition on an index column can be
used with a B-tree index.
To get proof, disable sequential scans in a test session (only!).
SET enable_seqscan = OFF;
I quote the manual here:
enable_seqscan (boolean)
...
7
The problem
Here is a very similar case discussed on pgsql.general. It's about the limitation in a b-tree index, but it's all the same because a GIN index uses a b-tree index for keys internally and therefore runs into the same limitation for key size (instead of item size in a plain b-tree index).
I quote the manual about GIN index implementation:
...
6
Assuming your table is named readings:
Find cities where at least one temperature is bigger than 80:
select *
from readings r
where exists (select 1
from unnest(r.temps) i
where i > 80)
Find cities where all temperatures are bigger than 68:
select *
from readings r
where 68 < all (select i
from ...
6
Deferred indexing would be nice, but isn't currently supported.
Adding indexes has a cost - write performance. They're a trade-off.
COPY won't help much if index maintenance is the main issue.
The simplest solution is to drop the indexes, and re-create them when you're done importing.
Since you can live with losing all your data if the DB crashes, you ...
6
Answer
The error occurs here:
CASE tmp_code
WHEN COALESCE(tmp_code,0)=0 THEN
Would have to be
CASE WHEN COALESCE(tmp_code,0)=0 THEN
You are mixing the two different syntax variants of PL/pgSQL CASE ("simple case" vs. "searched case") in an incompatible way.
Another error
There is another error:
update netcen.test set
test=myobj.test,
...
5
While klin is technically right in his answer about how to fix your current function, let me question your whole approach. What you try to achieve is called 'UPSERT' and with PostgreSQL 9.1 (which offers writable CTEs) you have a very simple way to achieve this. I omitted the function definition for the sake of clarity, but you can easily wrap it in a ...
4
Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique.
The side effect of this is that queries via primary and unique keys are usually "fast".
Now if you haven't defined primary or unique keys on your tables,
You don't have a relational table but you have junk (OK, ...
4
As an alternative to a_horse_with_no_name's solution, the simplest option is to use < and the any row-or-array comparision with the array:
SELECT city FROM cities WHERE 80 < any (temps);
See SQLFiddle.
It is not necessary to unnest the array, as any and all work on arrays as well as on rowsets.
Unlike using the array operators, this operation does ...
4
The following should do it:
Shut down PostgreSQL
Make sure PostgreSQL does not run any longer
Check that PostgreSQL is really stopped
Copy the old data directory to the new drive This is usually defined through a commandline parameter (-D) for your service or through the PGDATA environment variable.
Update your PostgreSQL configuration (service, ...
4
You will gain a lot more with regards to performance if you move the table to a different harddisk. As long as the "busy" table and the rest are located on the same disk, moving that table into a different "file" (by moving it into a different database) won't change anything with regards to (I/O) performance.
Distributing the I/O load to a different ...
4
\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs):
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------+------------------+--------------------------+--------
...
public | decrypt ...
4
What about:
with tweets(type, id) as (
select 'add' as type, tweets.id from tweets
) select row_to_json(row(array_agg(tweets))) from tweets;
This is actually one of the really cool things about PostgreSQL, arrays, and complex types. One can aggregate them and then convert. You should get a structure which is effectively:
tweets[] (i.e. an array ...
4
Will/can Solr/Lucene searches be faster than PostgreSQL even if no
full-text search is involved?
Yes. As per your quoted example, it can be many times faster than a relational database for certain use cases. Not surprising really.
Solr is a search engine. PostgreSQL is a relational database engine.
Solr is built from the ground up to do one thing ...
4
I can see how one might think listen_addresses = 'localhost' is somehow related to the authentication method local. But it is not. I quote the manual on listen_addresses:
listen_addresses (string)
Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications.
And the chapter The pg_hba.conf File in the ...
4
This sort of thing gets complicated. I am working on some related projects right now. The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to represent literal values, so:
SELECT save_book('(179,the art of war,fiction,"{190,220}")'::book);
should work. In essence a neat trick is creating a csv and ...
4
Assuming the locale of the database is en_GB.UTF-8 both in Ubuntu (master) and FreeBSD (slave), I believe the differences in sort semantics alone may account for the fact that the index is unusable on the slave.
Here's an example of how they sort differently:
On Ubuntu 12.04:
$ export LANG=en_GB.UTF-8
$ cat >file
"0102"
0102
$ sort file
0102
"0102"
...
4
First don't make plpython3u into a trusted language. This is bad. You are giving access to all kinds of things on the database, the filesystem, and the like in ways that could be horribly abused. Please reconsider.
Now from your comments you want to keep the admin permission separate from the normal user permission. The way to do this is to make sure ...
4
I think yes. Haven't tried with actual INSERTs or UPDATEs yet, but this function works:
CREATE TABLE a (id integer);
CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS
$body$
SELECT b.*
FROM unnest($1) b;
$body$
LANGUAGE sql;
This way you can write your INSERT statement as
INSERT INTO tbl_weightment
SELECT (r).*
FROM unnest(rec) ...
3
In PostgreSQL, inserting a column named
"ID"
is one thing. Inserting a column named
ID
is another.
create table test (
"ID" integer not null
);
insert into test values (1);
select *
from test
where ID = 1;
ERROR: column "id" does not exist
Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to ...
3
I think that in this case you are better off optimizing I/O generally, and trying to ensure you have a lot of RAM in your server than moving the tables onto another tablespace. If the tables are more frequently accessed and there is enough memory they will likely mostly be kept in memory and the WAL is the only file flushed to disk on commit anyway.
More ...
3
Like willglynn said, it's probably your pg_hba.conf file.
If you have the following line:
local all all peer
then change it to:
local all all md5
That should then let you login with your new password (assuming that you correctly supply it) :)
3
The problem occurs when using the Set Returning Function in the SELECT target-list, but that way of calling these functions is kludgy in the first place.
See for example this thread on pg-hackers list on various reasons why experienced PG users tend to avoid it.
On the other hand, when the SRF is called in the FROM list, without the fake cross join, 9.1.6 ...
3
Yes you will have to give the slave a new base backup (for streaming replication only steps 1 to 4) of the master.
Your problem has probably occured because the value of wal_keep_segments is to low. The value needs to be high enough that when the slave is down for some time the master won't start recycling segments the slave hasn't processed yet.
3
Please read this carefully regarding security considerations. Essentially you are trying to inject arbitrary SQL in your functions. Consequently you need to have this run under a user with highly restricted permissions.
Create a user and revoke all permissions from it. Do not grant permissions to public in the same db as you do this.
Create a function ...
3
First, you have to be able to connect to the database in order to run queries. This can be achieved by
REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;
GRANT CONNECT
ON DATABASE database_name
TO user_name;
The REVOKE is necessary because
The key word PUBLIC indicates that the privileges are to be granted to
all roles, including those that ...
3
While I don't see the real advantage of your solution, I mean passing a row to the function instead of passing the individual values as in
CREATE OR REPLACE FUNCTION save_book2(
integer
, text
, text
, integer[]
)
RETURNS text AS
...
Anyway, your solution works as well if you call the function correctly:
SELECT ave_book((179, 'the art ...
3
If you ever wonder about the correct syntax for a row type, ask Postgres. It should know:
SELECT b FROM book b LIMIT 1; -- or: WHERE id = 179;
Which will return a text representation of your row in valid format:
(179,"the art of war",fiction,"{190,220}")
Values of columns are represented as an unquoted, comma-separated list, enclosed in paretheses.
...
3
VOLATILE is completely independent from COST.
The first defines function volatility and VOLATILE is the default, so it can be omitted. Alternatives are STABLE and IMMUTABLE.
The latter is only valid in combination with a COST parameter, like COST 500. The default is COST 100 which, again, can be omitted. It declares the cost per row of the result, which is ...
3
To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA
select schema_name
from information_schema.schemata
More details in the manual: http://www.postgresql.org/docs/current/static/information-schema.html
alternatively:
select nspname
from pg_catalog.pg_namespace;
More details about pg_catalog in the manual: ...
Only top voted, non community-wiki answers of a minimum length are eligible
