Tag Info

New answers tagged

0

Size of the table is 53Gb without indexes. Obviously you have indexes, one of them a GIN index, which is typically multiple times the size of a plain table storing only the indexed column. I expect you get around 180 GB for SELECT pg_total_relation_size(osmm.topographicarea); Details on measuring sizes: Measure the size of a PostgreSQL table row As ...


1

They are the same thing in a default installation. pg_catalog.text is the schema-qualified version of the type name text. The search_path always contains pg_catalog automatically. Per documentation: In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data ...


0

Update postgres' home directory to point to the correct database location. sudo usermod --home '/path/to/database' postgres


2

Not a direct answer to your question but you should try the first_value window function. It works like this: CREATE TABLE test ( id SERIAL NOT NULL PRIMARY KEY, cat TEXT, value VARCHAR(2) date TIMESTAMP WITH TIME ZONE ); Then, if you want the first item in each cat (category) you will query like that: SELECT cat, ...


0

Is the context a big overhead? Can I use it for realtime data mapping (let's say 1000 queries/s)) Performance depends on hardware and complexity of your functions. I created an appliance that ran on a small 12-core server and a FusionIO-card (total costs euro 10000) and did about 2500 transactions per second with 20 concurrent users. Each transaction ...


0

To save bytea to the disk server-side, it is necessary to be a database superuser. Normal users are not allowed to write to the filesystem. Assuming superuser rights, the simplest way is to implement it as a function in one of the "untrusted" languages. Example in pl/perlu: CREATE FUNCTION bytea_to_file(bytea,text) RETURNS void AS $$ open(my $fd, ...


2

Take a look here. It's a tool which genuinely tries to compare like with like in terms of database benchmarking!


2

well, you cannot switch back easily. what you can do is to use the old backup method: SELECT pg_start_backup('whatever'); rsync the data dir SELECT pg_stop_backup(); this is the best way as not all the data has to be transported over the network. i definitely recommend this replacement for pg_basebackup because it is the only RELIABLE way to get things ...


1

in PostgreSQL you should use a tool called pgbench. it allows you to run custom scripts with an arbitrary number of concurrent requests and all that. it is also able to create random values for your script and all that - it is really powerful. for the test: make sure you got a proper setting for -j (number of threads used by pgbench) so that pgbench is able ...


6

select string_agg(c,'') from ( select distinct regexp_split_to_table(lower(name),'') as c from data ) t The inner select generates one row for each character, and the outer then aggregates that to a long string. If you want the characters sorted, you can use an order by for the aggregate string_agg(c,'' order by c)


0

Open postgresql.conf, go to the line: data_directory='/var/lib/postgresql/main/' is your answer.


3

Yes you can. Per documentation: Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the ...


1

Issue ALTER ROLE client VALID UNTIL 'infinity' and it will remove the VALID UNTIL clause.


2

There is no definite answer to whether "it is worth the trouble". That's really a matter of taste and style and the total picture we don't have. If in doubt, just don't bother, that's typically the best course of action. For small, simple tables where you rarely delete rows, it might be convenient for human users to remove gaps from primary keys. However, ...


0

Answering my own question...but after playing around with it for awhile this seems to provide the correct answer: SELECT MIN(timestamp) as "Date", "EmailEvents".send_name as "Send Name", cohort as "Cohort", product as "Product", COUNT(DISTINCT email) AS "Send Size", (100*openCount.distinctCount / COUNT(DISTINCT email))::numeric AS "Open Rate" ...


5

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation. This created an artificial limit on the number of tuples which could be sorted in memory. Once that limit was reached, it had to switch to a disk sort, even if there was memory left over. This restriction was removed in version 9.4.


0

If Your application can get data from more than one database You can use cascading replication for load balancing(One App get data from multiple bases) but only in case when it's synchronous replication(in case of cascading rep. it's heavy). In case of M1 fail You can do Python script that will place S1 as master(including updating port, config, and ...


2

I would look into partitioning. If partitioned by day, you could just drop the entire partition once it gets too old. You may even no longer have to vacuum. Also, overall performance might increase, since you're not inserting where you're deleting. You would just need to write the code to create new partitions and delete old ones. This is exactly what ...


0

Index usage depends on the structure of you data and the selectivity of you data. If you make queries that you don't use them they are expensive. If you use them effectively they are great tool. There is a query planner and query planner analytics(EXPLAIN) of just planning or planning and executuion of commands (EXPLAIN ANALYZE). And there is a great ...


2

Either way, that's totally possible, given that all your parameters are of the same data type. EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts. create or replace function test_function(_filter1 text = null , _filter2 text = null ...


0

I don't know of any way to accomplish this purely through psql, but using bash, you can list the tables in database $DB with: psql -tc "select tablename from pg_tables where schemaname = 'public';" $DB And the ownership can be transferred to $OWNER with: psql -c "alter table $table owner to $OWNER" $DB Stringing this together gives you: $ for table ...


3

As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped ...


0

PostgreSQL is not so far beyond MySQL, and there is plenty of documentation and a variety of GUIs to work with. Check out NaviCat, it's very helpful (though pricy).


4

It shouldn't take long to get up to speed to a reasonable degree if you've used another RDBMS. Read some guidance on PostgreSQL for MySQL users to help you adapt to sequences vs auto_increment, ANSI-standard quoting (though you should be using that in MySQL already), the stricter data type checking, how authentication and roles work, psql's backslash ...


1

On OS X 10.8 and 10.9 (not 10.10) with server app installed, in a terminal window, type sudo serveradmin settings postgres On OS X 10.9 Mavericks, this is the output I get from that, which includes the data directory. postgres:log_connections = "on" postgres:unix_socket_directory = "/private/var/pgsql_socket" postgres:listen_addresses = "127.0.0.1,::1" ...


1

It actually suffixes the password with the username and then MD5 hash is made out of that, and that hash is then prefixed with 'md5'


0

The PostgreSQL 9.3 Documentation has outlined how to alter default permissions. Follow this link to learn more! Here is an excerpt that demonstrates how one would change the default permissions for a GROUP: ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] abbreviated_grant_or_revoke ...


1

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months. The relationship between dates and these units was documented, but really messy. So here is what we did. We created a table, call it Almanac, with one row per date. (In reality, we had one row per work ...


0

this is exactly what you are facing: test=# CREATE SEQUENCE seq_a; CREATE SEQUENCE test=# SELECT nextval('seq_a'); nextval --------- 1 (1 row) test=# BEGIN; BEGIN test=# SELECT nextval('seq_a'); nextval --------- 2 (1 row) test=# ROLLBACK; ROLLBACK test=# SELECT nextval('seq_a'); nextval --------- 3 (1 row) a sequence makes ...


1

No. How would the query processor recognize that it should use the materialized view. The materialized view is another object. See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view." So materialized ...


1

Answer to own question: I was blind. The statements are not truncated, but they contain newlines. There was some pre preprocessing done before I looked at them. And this preprocessing was done lines based, so they looked like being cut.


2

So, as mentioned in the comments, since you apparently have the authority to modify the data model itself, there are perhaps better solutions available to you at this point. The direct answer to your question is to update your new prices table to reference your preferred products record in each duplication case, then purge the other duplicated products ...


2

Well, Apologies to all those SQL guru's - this might be a simplistic answer. Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key. Insert into that table a distinct list of the products e.g INSERT INTO products (sku,ProductName) SELECT DISTINCT (sku,ProductName) FROM ...


1

well, it depends a bit on the use case. reusing item pointers in GIN is definitely a lot harder than it is in a btree because the structure is totally different on the item pointer level. with the new GIN structure life has become even harder to predict so i would keep a close eye on it - depending on your way of using GIN a reindex might be necessary. but ...


0

you can make use of the so called ctid (which is basically an internal thing but for a lousy hack it should do. here is an example: ' test=# CREATE TABLE x (id int); CREATE TABLE test=# INSERT INTO x SELECT * FROM generate_series(1, 4); INSERT 0 4 test=# INSERT INTO x SELECT * FROM generate_series(1, 4); INSERT 0 4 test=# SELECT ctid, * FROM x; ctid | id ...


1

GIN indexes are built using B-trees, but with a different structure. "The potential for bloat in non-B-tree indexes has not been well researched. It is a good idea to periodically monitor the index's physical size when using any non-B-tree index type." See more at: Routine reindex


0

Use timestamp ranges for booking availability. There is good documentation in official PostgreSQL docs. You may find this lecture helpful: Range Types and Temporal: Past, Present, and Future And my blog post about generating timestamp ranges could also be helpful: Generating ranges in PostgreSQL


1

Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird. pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same ...


1

It looks like what you want is row-level security. PostgreSQL will support this in version 9.5, but that's due to come out in over a year. For now, you'll need to use views. If you don't mind the fact that a clever user can trick the system into revealing information they're not meant to be able to see using malicious functions in the WHERE clause, you can ...


1

If you use EXPLAIN you'll see the ordering. For a random plan I concocted with some throwaway data and similar query (ORDER BY ... LIMIT 1 FOR UPDATE) I got the plan: Limit (cost=33.09..33.10 rows=1 width=26) -> LockRows (cost=33.09..39.88 rows=543 width=26) -> Sort (cost=33.09..34.45 rows=543 width=26) Sort Key: t_id ...


1

For Question 1: using md5 auth for local connections is particularly helpful when you have a multi-user machine, or if you might add additional user accounts for friends/family/coworkers/whoever someday. If you are and will remain the only actual user on your system, there is less benefit, but it could still be useful: suppose some bad guy manages to break ...


1

I believe the answer is in this header comment of heaptuple.c: * Before Postgres 8.3 varlenas always had a 4-byte length header, and * therefore always needed 4-byte alignment (at least). This wasted space * for short varlenas, for example CHAR(1) took 5 bytes and could need up to * 3 additional padding bytes for alignment. * * Now, a short varlena (up to ...


0

no, pgstrom cannot do that. it only able to scan relations - it is not able to join. what you an do is pack an operator into a GPU routine but = is not a good candidate for that.


3

if you add a second disk i suggest to put data on to different tablespaces. a tablespace offers additional storage and is VERY easy to use: mkdir /whatever then in SQL: CREATE TABLESPACE myspace LOCATION '/whatever'; ALTER DATABASE x SET TABLESPACE myspace; of course you can also do this for single tables only. and yes, alternatively you can move the ...


1

Table layout It would be more efficient to reverse the column order in nodes: fixed length NOT NULL columns first. This is just a tiny optimization. It's only the first item because table layout comes first. CREATE TABLE nodes( id bigint PRIMARY KEY , type text NOT NULL , name text ); Index Replace substring(name,1,2700) with left(name, 2700) ...


2

Option 1: remove createdb for 9.1 When in doubt, use dpkg -S to learn which packages provide a certain command. Example: $ dpkg -S createdb postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/tutorial-createdb.html postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/app-createdb.html postgresql-client-9.1: ...


1

Thx Mladen, I think renaming the table after dump operation is easier than altering the sql dump file itself. Following is my Python script to do the same. import time import psycopg2 import os connection3 = psycopg2.connect("host='localhost' dbname='db_archieve' user='postgres' password='pass123'") cursor5 = connection3.cursor() os.system('pg_dump -U ...


2

For PostgreSQL 9.2 and above there is no postgres system user (by default). It runs as NETWORK SERVICE. Create d:\tablespaces and give the NETWORK SERVICE (SERVICE RÉSEAU in french) "Full control" over it. That should do the trick.


1

The way statement_timeout works, the time starts counting when the server receives a new command from the client. Queries launches inside server-side functions are not commands from a client, they don't reset that timer or push a new one onto a stack of timers. This is why SET LOCAL statement_timeout = 100; has no effect. And if a function does SET ...


1

I strongly believe that only the "local" record is set to trust. try something like that: local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust this should work for all kinds of local connections.



Top 50 recent answers are included