3manuek
  • Member for 6 years, 5 months
  • Last seen more than a month ago
Amazon RDS Postgresql adding new extensions
15 votes

Currently there are a few available extensions in Amazon RDS -- detailed in the "Database Engine Features" chapter. You can issue the following command to get the available extensions supported: ...

View answer
MariaDB Can't init tc log
9 votes

You can remove the tc.log in the data directory and remove old entries from mysql-bin.index (it is a text file, along with a list of binary logs). If this is a development box, you can remove the ...

View answer
Does changing `wal_keep_segments` require a restart?
Accepted answer
9 votes

For checking the variable's context, you can execute the following query: SELECT name, context FROM pg_settings WHERE name = '<variable name'; In the case of wal_keep_segments, the context is ...

View answer
postgres_fdw performance is slow
Accepted answer
8 votes

If you use use_remote_estimate be sure to run ANALYZE the foreign table (I see estimations pretty close with the returned, you'd probably did it). Also, the pushdown improvements are not available in ...

View answer
MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed
4 votes

The way that both RDBMS do the count differs. In InnoDB we have the following behaviour by default: To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes ...

View answer
Cassandra "Operation timed out - received only 0 responses." during write
4 votes

You will need to increase commitlog_segment_size_in_mb to a higher value. Try 64MB instead. It increases the size of the commit record segment. This property determines the maximum mutation size (...

View answer
Restrict Postgres roles by IP address outside of the pg_hba.conf file
Accepted answer
4 votes

There is no way to do this with native SQL in PostgreSQL. Also, Postgres manages the privileges and access in a very different way than MySQL. I've built a VERY BASIC function (needs a lot of tweak ...

View answer
replicate-ignore-table setup and usage for excluding table from binlog backup
Accepted answer
3 votes

The replicate-ignore-table filter applies only the streaming over replication protocol (specifically in this case the SQL thread Slave), it doesn't affect if you apply log changes manually as you are ...

View answer
Normalize repeated indefinite ongoing dates for an event
3 votes

I think that at the end, event_dates will need to be partitioned. That will allow you to archive old events. form_control may need this also, and probably primary_event_date_id could be a candidate. ...

View answer
Upgrading Greenplum DB that includes PostGIS extension
2 votes

My bet is that the postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg package has a bug and it does not have implemented the postupdate as defined in the Gppkg class. The TAG is optional (...

View answer
How to determine optimal value for pg_stat_ramdisk_size on Amazon RDS?
Accepted answer
2 votes

The pg_stat_ramdisk_size controls the location and size of the stats_temp_directory on RAM. It's easy to determine the size on bare metal or out of the RDS byt issuing a du -h over the directory ...

View answer
Simple queries to Redshift really slow
Accepted answer
2 votes

The whole idea around Redshift is not to run in a single node. It is actually designed to run in a sharded cluster and it is expected to have very bad numbers within only one node. If you look at the ...

View answer
Permission Denied on shared object (.so) file postgresql
Accepted answer
2 votes

Permissions on the path and file should be postgres:postgres. ls -l /home/pedro/.../.../example.so chown -R postgres: /home/pedro/.../.../ You will probably need sudo for changing the file ownership....

View answer
mysql replication - missing table for foreign key on slave
2 votes

The exact reason of why this happens, is because SESSION variables are replicated explicitly in the binary log. Here is an example of an entry header in the binary log: # at 368008153 #160624 9:15:...

View answer
MySQL replication conflicts
2 votes

The root cause could be very varying, specially if your slave is not in read_only mode. Clearly, the table has a data drift (means that both master and slave tables have different contents). I ...

View answer
Read-only replica of RDS Postrgres instance with modified schema
1 votes

You can't do this in RDS at the moment. You can spin a replica as a mirrored database but it does not allow the advanced features for logical decoding or partial replication. Unfortunately this is the ...

View answer
Subnetting IPv6 in Postgres
1 votes

Well, Postgres supports Network Operators and Functions and IPv6 isn't the exception. Actually your question is a good one, as I didn't see much examples around on how to do this (explicitly with IPv6)...

View answer
MySQL 5.6 InnoDB doesn't work
1 votes

Clearly the data you are trying to start does not belong to the configuration you have in the my.cnf. If you look at the beginning of the log, you will see that there is a mismatch between the files ...

View answer
MySQL locking in Duplicate Key Error
1 votes

I did a small simulation of that situation, in a simple bash script that does that: ➜ rsandbox_5_6_30 cat test.sh ./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(3); rollback;"...

View answer
MySQL Online DDL Index Creation
1 votes

That happens when InnoDB compressed tables with large amount of rows are being updated (no matter if the algorithm is in place). Large operations for ALTER tables, will use the "online log", which is ...

View answer
What's the quickest way to periodically export PostgreSQL data to a local file on Windows?
1 votes

The best way to copy a result set into a file is through COPY. i.e.: COPY (SELECT ...) TO 'filename.csv'; To load the data, you can use the FROM clause. This command is Operating System agnostic, ...

View answer
investigate contents of a sqlite3 file
0 votes

I had a file that was around 30 MB, and issued the vacuum full (not the normal VACUUM as it has the same effect that the Postgres behavior) for reducing its size. After the operation the file got ...

View answer
Why am I getting the access denied errors after login?
0 votes

Looks like your user only have USAGE privilege,which is not enough for executing these operations. If this is a development instance,you can proceed to grant ALL privileges to that user across all ...

View answer
PostgreSQL GIN index not used when ts_query language is fetched from a column
0 votes

It has sense that Pg is forcing a seqscan when querying the language column using to_tsquery as every iteration needs the value of the language columns of all the examined rows. For this particular ...

View answer
"Lambda" function returns type string instead of type record
0 votes

The reason is that your EXECUTE is returning a single column. I did a code like this one and it worked: SELECT array_to_string(inputs,',') INTO columns; EXECUTE 'SELECT ' || columns INTO out; I ...

View answer
What is the best way to have full text search where data in database is frequently updated
0 votes

Are you open to use other techs as elasticsearch or sphinx? When write scalability problems hit, it'll be harder for you to scale your MySQL. Unless you strictly need to have transactional FULL TEXT ...

View answer
MySQL - run SELECT statement on another server without defining the table structure
0 votes

TL; DR; No, you can't. -- You can't run arbitrary SQL against a remote server table without using FEDERATED. See this response. Although, I don't see the problem with defining locally the tables, ...

View answer
generate_series for multiple record types in postgresql
0 votes

I will suggest next time that you use fiddle.com in order to have an online schema to play with. generate_series function returns a set of timestamp, so you will need to cast it to date outside the ...

View answer
Speeding Up MySQL Full Text Search
0 votes

I would recommend to reduce the amount of occurrences in the inner query over phppos_items. A way to do that is to get only the heavier records using a subquery. I see that you are using LIMIT 20, so ...

View answer